Posts Learn Components Snippets Categories Tags Tools new About
/

Laravel Subquery Code Example

Code example for writing a subquery in Laravel using DB facade

Created on Nov 13, 2021

8 views

Imagine having RAW SQL like the following where you are explicitly selecting columns from a table and from within the table that you select itself has a subquery that selects another table with different conditions.
SELECT 
    `p`.`id`,
    `p`.`name`, 
    `p`.`img`, 
    `p`.`safe_name`, 
    `p`.`sku`, 
    `p`.`productstatusid` 
FROM `products` p 
WHERE `p`.`id` IN (
    SELECT 
        `product_id` 
    FROM `product_category`
    WHERE `category_id` IN ('223', '15')
)
AND `p`.`active`=1
This RAW SQL query can easily be written in Laravel and to achieve this in Laravel you can make use of the DB facade. Here's an example of how you can convert the RAW SQL above into Laravel Fluent.
<?php

use \DB;

DB::table('users')
    ->whereIn('id', function($query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereRaw('orders.user_id = users.id');
    })
    ->get();

The above code will produce the following SQL code.
select * from users where id in (
    select 1 from orders where orders.user_id = users.id
)
For more information do have a look at the advanced where clause documentation for Fluent.

If you like our tutorial, do make sure to support us by being our Patreon or buy us some coffee ☕️

)