How to write a Join in Laravel with Nested (Second) SQL Statement

Use Case

Let’s say that you sell a product on your website and you want to generate a list of the users referred by Jane Smith, as well as how many times they have ordered. There will be some users who Jane Smith referred but haven’t completed an order yet, so we need that left join to include them too.

For your template you want to output the users ID number, their name, and the number of orders placed with you. For this we will use existing tables users and orders.

Assume that the orders table has fields id, user_id (and also order-related details like $ total) and that the users table has fields id, name, referrer_id.

 The Raw SQL

(Jane Smith’s unique ID is $referrer_id):

$sql = "SELECT users.id , users.name, orders.num_orders
	FROM users
	LEFT JOIN (
			SELECT user_id, count( * ) AS num_orders
			FROM orders
			GROUP BY user_id
		) orders ON orders.user_id = users.id

	WHERE referrer_id = '{$referrer_id}'";

// Now run the query

Using Eloquent in Laravel 5

Take care to avoid SQL injections when using DB::raw().

$temptable = DB::raw("(SELECT user_id, count(*) AS num_orders 
		FROM orders GROUP BY user_id) as orders");
		
return DB::table('users')
	->select('users.id','users.name', 'orders.num_orders')
	->leftJoin($temptable, 'orders.user_id', '=', 'users.id') 
	->where('referrer_id', '=', $referrer_id)
	->get();

Sample Output

(test data courtesy of Faker):

array (size=5)
  0 => 
    object(stdClass)[187]
      public 'id' => string '16' (length=2)
      public 'name' => string 'Stoltenberg, Sanford And Pfeffer' (length=32)
      public 'num_collections' => string '1' (length=1)
  1 => 
    object(stdClass)[186]
      public 'id' => string '20' (length=2)
      public 'name' => string 'Ebert, Daugherty And Mann' (length=25)
      public 'num_orders' => string '2' (length=1)
  2 => 
    object(stdClass)[185]
      public 'id' => string '23' (length=2)
      public 'name' => string 'Daugherty LLC' (length=13)
      public 'num_orders' => string '1' (length=1)
  3 => 
    object(stdClass)[195]
      public 'id' => string '24' (length=2)
      public 'name' => string 'Reilly-Greenfelder' (length=20)
      public 'num_orders' => null
  4 => 
    object(stdClass)[196]
      public 'id' => string '27' (length=2)
      public 'name' => string 'Breitenberg-Wilkinson' (length=21)
      public 'num_orders' => null

Last updated on September 23rd, 2015