How to select max aggregate within a subquery with a join in eloquent

Asked by: Yash Sanghvi

I want to select the most recent entry but there are no timestamps(created_at) it can be identified by selecting the max on the primary key. The problem is to this within a subquery and tables needs to be joined.

The query is like:

        $prodarr = DB::table('onhand_inventory')
            ->leftJoin('mapping', 'onhand_inventory.sku', '=', 'mapping.sku')
            ->where('mapping.mid', function ($query) {
                $query->select('mapping.mid')->from('mapping')
                    ->whereRaw('onhand_inventory.sku = mapping.sku')
                    ->max('mapping.mid');
            })->leftJoin('order_products', 'order_products.sku', '=', 'onhand_inventory.sku')
            ->leftJoin('vendors', 'mapping.v_id', '=', 'vendors.vid')
            ->where('onhand_inventory.shipped_with_box', 0)
            ->groupBy('onhand_inventory.inv_id')
            ->orderBy('onhand_inventory.inv_id')
            ->get();

On executing the above query following error is thrown Column not found: 1054 Unknown column 'onhand_inventory.sku' in 'where clause'

How to get the onhand_inventory's data to be compared with the mapping's inside the subquery.

Source