$results = DB::select('select * COMPLICATED QUERY = ?');

Ok I will try this but I was looking for a query builder solution instead. But again, thank you for your answer.

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );

You can also use a raw query.

I see you are using some sub-queries in your SQL statement.

Me personally, I would break them out, use Eloquent to accomplish the goal of each sub-query and attach them as static methods to the appropriate models.That way, you could use those methods as callbacks to your higher level function.

I know I didn't really answer your question, but have you taken that approach yet?

Wouldn't that impact performance if the database is huge?

Your query above already packs quite a punch. But you are right, it is a double edged sword. You will want to use some of the eager loading techniques that Laravel provides, and if done right, the performance hit should not even be noticeable. And you will have re-usable code.

I think this is already out of what OP's question is, but,

In my opinion, I personally don't like the idea of using Eager Loading when dealing with complex queries. Mainly because server roundtrips are generally expensive, especially when your DBMS connection is not coming from the same source as your application server.

Secondly, RDBMS query optimizer has their own query execution plan that optimize your query.

Since you already have written that complex query, I would say it's actually better to stick with what @alimccutchion said. Just my 2 cents... :P

DonaldRecord said:

I see you are using some sub-queries in your SQL statement.

Me personally, I would break them out, use Eloquent to accomplish the goal of each sub-query and attach them as static methods to the appropriate models.That way, you could use those methods as callbacks to your higher level function.

I know I didn't really answer your question, but have you taken that approach yet?

To be honest for such complex query I am not able to translate the SQL query to Eloquent. If you can help me to understand how to do that I am not close ;-).