Laravel has a powerful query builder but there are limitations when database queries become more complex. Enter Raw queries.
The docs have an example of a raw query which will get you going but sometimes there is a need to completely ditch the builder and and go it alone.
The following example is what I used create a query which is completely raw.
DB::select(DB::raw('
SELECT Months.id AS `month` ,
COUNT(story.id) AS `count`
FROM
(
SELECT 1 as ID UNION SELECT 2 as ID UNION SELECT 3 as ID UNION SELECT 4 as ID
UNION
SELECT 5 as ID UNION SELECT 6 as ID UNION SELECT 7 as ID UNION SELECT 8 as ID
UNION
SELECT 9 as ID UNION SELECT 10 as ID UNION SELECT 11 as ID UNION SELECT 12 as ID
) as Months
LEFT JOIN `story` on Months.id=month(story.created_at)
AND
(company_id = :companyId)
AND (YEAR(created_at)= :thisYear)
GROUP BY Months.id
ORDER BY Months.id ASC'), array('companyId'=>Auth::user()->company_id, 'thisYear'=>Carbon::now()->year));
The important thing to notice here is that when using the raw query you can pass variables straight into the query which could be a security issue especially if the information is being passed from a from by a user.
To ensure that any values that need to be passed to the query are sanitized they can be passed in an array after the query.
Take a look at the query and the ‘And’ clauses.
company_id = :companyId
The :companyId in the example is how you add your variables using the array.
...ORDER BY Months.id ASC'), array('companyId'=>Auth::user()->company_id, 'thisYear'=>Carbon::now()->year));
In the array give the key and then the value to be added.
That is it. Hope it helps with your complex Laravel queries.