New database feature: Subqueries

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

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

$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

$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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>