The database module now select subqueries, which means you can use query builder to write practically any query you like. Let’s take a quick look at some new features:
Subqueries in conditional statements

1
2
3
4
5
6
7
8
DB::query('select')
    ->table('fairies')

    //WHERE id in (SELECT fairy_id from trees)
    ->where('id','IN',DB::query('select')
            ->fields('fairy_id')
            ->table('trees')
        );
 

Subqueries as tables

1
2
3
4
5
6
7
8
9
10
$pixies=DB::query('select')
    ->table('fairies')
    ->where('type','pixie');

DB::query('select')
    //Specifying an alias for the subquery select
    ->table($pixies,'pixies')
    ->where('pixies.id','>',7);

It is also possible to pass subqueries into join() in a similar way
 

UNION queries

1
2
3
4
5
6
7
8
$pixies=DB::query('select')
    ->table('fairies')
    ->where('type','pixie');

DB::query('select')
    ->table('fairies')
    ->where('type','fairy')
    ->union($pixies);
 

To see more examples check out the updated Database Tutorial.