array-query-builder.php

<?php

// SELECT a.*, c.d, (c.e = 'foo') AS ee, c.f AS ff
// FROM table1 a, table3 m
// LEFT JOIN table2 b ON a.x = c.x AND a.x <> '4'
// WHERE m.x = a.x AND a.y < 100 AND (a.z BETWEEN 1 AND 9 OR a.z = -1)
// GROUP BY (c.e = 'foo'), c.f
// HAVING MAX(m.n) > 2
// ORDER BY c.e DESC
// LIMIT 100
// OFFSET 100

$query = array(
	// SELECT
	'fields' => array(
		'a' => array('*'),
		'c' => array(
			'd',
			'ff' => 'f',
		),
		array(
			'ee' => array('(c.e = ?)', 'foo'),
		),
		// @todo buildable sub query?
	),
	// 'fields' => 'a.b, c.d',

	// FROM
	'tables' => array('b' => 'table1', 'm' => 'table3'),
	// 'tables' => 'table1 a, table3 m',
	// @todo buildable sub query?

	// X JOIN
	'join' => array(
		'b' => array(
			'type' => 'left',
			'table' => 'table2',
			'conditions' => array( // %conditions%
				'a.x = c.x',
				array('a.x <> ?', 4),
				// @todo buildable sub query?
			),
		),
	),

	// WHERE
	'conditions' => array( // %conditions%
		'and',
		'm.x = a.x',
		array('a.y', 100, '<'),
		// @todo buildable sub query?
		array(
			'or',
			array('a.z BETWEEN ? AND ?', 1, 9),
			array('a.z', -1),
		),
	),

	// GROUP BY
	'group' => array(
		array('(c.e = ?)', 'foo'),
		'c.f',
		// @todo buildable sub query?
	),

	// HAVING
	'having' => array( // %conditions%
		array('MAX(m.n) > ?', 2),
		// @todo buildable sub query?
	),

	// ORDER
	'order' => array(
		'c.e' => 'desc',
		// @todo buildable sub query?
	),

	// LIMIT
	'limit' => 100,

	// OFFSET
	'offset' => 100,
);