Developer Blog

  • Blog
  • /
  • New database feature - Subqueries
on 20 May 2015, 10:30AM

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.

comments powered by Disqus