How to write a eloquent sub query with from clause.

I have the below query which I use to get max 3 months sales date where quantity is greater than 0. The query itself is fine and I get the correct data but how can I write this plain query into Laravel with eloquent relation?

select * from (select productCode, quantity, salesDate from sales where productCode = "1001000029" and salesType=0 AND quantity>0 order by salesDate desc limit 3) sales

I already have a relational query, the main query is linked with sales.

$mainQuery loop and I get sales data within a loop like below. How can I write the above query within the below loop?

@foreach($mainQuery as $row)
	$salesdataByProduct = $row->sales()->find('1001000029'); // Example only
    // How can I write the below query here
@endforeach

LaryAI

3 years ago

Best Answer

Level 58

To write the given query in Laravel with Eloquent relation, you can use the selectRaw method to write the subquery and then use the fromSub method to use it as a subquery in the main query. Here's an example:

$subquery = DB::table('sales')
    ->select('productCode', 'quantity', 'salesDate')
    ->where('productCode', '1001000029')
    ->where('salesType', 0)
    ->where('quantity', '>', 0)
    ->orderByDesc('salesDate')
    ->limit(3);

$salesdataByProduct = $row->sales()
    ->select('*')
    ->fromSub($subquery, 'sales')
    ->get();

In this example, we first create the subquery using the DB::table method and the various query builder methods to build the query. We then use the selectRaw method to select the columns we want.

In the loop, we use the fromSub method to use the subquery as a table in the main query. We pass the subquery and a table alias to the fromSub method, and then use the select method to select all columns from the subquery.

Note that we use the get method instead of the find method to retrieve the results, since the subquery may return multiple rows.

1 like

Please or to participate in this conversation.