README

@see nette/database

API 扩展

LEFT JOIN 中的条件

条件连接到 LEFT JOIN 从句

...->left()

$selection = $context->table('book')
        ->left(':product_price.active', 1)
        ->select('book.*, :product_price.value');

SELECT book.*, product_price.value
FROM book
LEFT JOIN product_price ON book.id = product_price.book_id
AND (product_price.active = 1)

$selection = $context->table('book')
		->left(':product_price.active', 1)
		->where(':book_tag.tag.name LIKE', 'PHP')
		->left(':product_price.value > ?', 0)
		->left(':book_tag.tag_id IS NOT NULL')
		->select('book.*, :product_price.value');

SELECT book.*, product_price.value FROM book
LEFT JOIN book_tag ON book.id = book_tag.book_id AND (book_tag.tag_id IS NOT NULL)
LEFT JOIN tag ON book_tag.tag_id = tag.id
LEFT JOIN product_price ON book.id = product_price.book_id AND (product_price.active = 1 AND product_price.value > 0)
WHERE (tag.name LIKE "PHP")

条件可以随意混合，但需要注意，条件将根据条件中第一个列的顺序连接到 LEFT JOIN 从句（通常只有一个，所以无需处理）。

$selection = $context->table('book')
		->left(':product_price.active', 1)
		->left(':book_tag.tag_id IS NOT NULL OR :product_price.active IS NOT NULL') // bude pripojeno k book_tag
		->select('book.*, :product_price.value');

SELECT book.*, product_price.value FROM book
LEFT JOIN product_price ON book.id = product_price.book_id AND (product_price.active = 1)
LEFT JOIN book_tag ON book.id = book_tag.book_id AND (book_tag.tag_id IS NOT NULL OR product_price.active IS NOT NULL)

别名

...->alias()

$selection = $context
		->table('book')
		->alias(':product_price', 'pp')
		->left('pp.active', 1)
		->select('book.*, pp.value');

SELECT book.*, pp.value
FROM book
LEFT JOIN product_price AS pp ON book.id = pp.book_id AND (pp.active = 1)

$selection = $context->table('book')
		->alias(':product_price', 'pp')
		->left('pp.active', 1)
		->alias(':book_tag.tag', 't')
		->where('t.name LIKE', 'PHP')
		->left('pp.value > ?', 0)
		->left(':book_tag.tag_id IS NOT NULL')
		->select('book.*, pp.value');

SELECT book.*, pp.value FROM book
LEFT JOIN book_tag ON book.id = book_tag.book_id AND (book_tag.tag_id IS NOT NULL)
LEFT JOIN tag AS t ON book_tag.tag_id = t.id
LEFT JOIN product_price AS pp ON book.id = pp.book_id AND (pp.active = 1 AND pp.value > 0)
WHERE (t.name LIKE "PHP")

使用别名的 LEFT JOIN，但选择的是非别名的

$selection = $context
		->table('book')
		->alias(':product_price', 'pp')
		->left('pp.active', 1)
		->select('book.*, :product_price.value');//tricky

SELECT book.*, product_price.value
FROM book
LEFT JOIN product_price AS pp ON book.id = pp.book_id AND (pp.active = 1)
LEFT JOIN product_price ON book.id = product_price.book_id

从搜索中排除字符串

有时在子查询中需要使用表别名，并与之交互。经典的 Selection 会尝试查找此别名，这会导致错误

...->where('author.id = (SELECT b.author_id FROM book AS b LIMIT 1)');

解决方案是在 !b.author_id 前使用感叹号，整个看起来是这样的

$selection = $context->table('author')
        ->where('author.id = (SELECT !b.author_id FROM book AS b LIMIT 1)');

SELECT *
FROM author
WHERE (author.id = (SELECT b.author_id FROM book AS b LIMIT 1))

强制索引

类似于 SQL：确保使用所需的索引。

...->forceIndex()

$selection = $context->table('book')
		->forceIndex('use_this_index')
		->select('book.*');

SELECT book.* FROM book
FORCE INDEX (`use_this_index`)