esports/database

@see nette/database

Rozšíření API

Podmínka v LEFT JOIN

Podmínka je připojena ok LEFT JOIN klauzuli

…->left()

$choice = $context->desk(‘e book’) ->left(‘:product_price.lively’, 1) ->choose(‘e book.*, :product_price.worth’); SELECT e book.*, product_price.worth FROM e book LEFT JOIN product_price ON e book.id = product_price.book_id AND (product_price.lively = 1) $choice = $context->desk(‘e book’) ->left(‘:product_price.lively’, 1) ->the place(‘:book_tag.tag.title LIKE’, ‘PHP’) ->left(‘:product_price.worth > ?’, 0) ->left(‘:book_tag.tag_id IS NOT NULL’) ->choose(‘e book.*, :product_price.worth’); SELECT e book.*, product_price.worth FROM e book LEFT JOIN book_tag ON e 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 e book.id = product_price.book_id AND (product_price.lively = 1 AND product_price.worth > 0) WHERE (tag.title LIKE “PHP”)

Podmínky lze libovolně míchat, jenom je třeba mít na paměti, že bude podmínka připojena ok LEFT JOIN klauzuli podle prvního sloupečku v podmínce (obvykle je uveden jenom jeden, takže netřeba řešit).

$choice = $context->desk(‘e book’) ->left(‘:product_price.lively’, 1) ->left(‘:book_tag.tag_id IS NOT NULL OR :product_price.lively IS NOT NULL’) // bude pripojeno ok book_tag ->choose(‘e book.*, :product_price.worth’); SELECT e book.*, product_price.worth FROM e book LEFT JOIN product_price ON e book.id = product_price.book_id AND (product_price.lively = 1) LEFT JOIN book_tag ON e book.id = book_tag.book_id AND (book_tag.tag_id IS NOT NULL OR product_price.lively IS NOT NULL)

Aliasování

…->alias()

$choice = $context ->desk(‘e book’) ->alias(‘:product_price’, ‘pp’) ->left(‘pp.lively’, 1) ->choose(‘e book.*, pp.worth’); SELECT e book.*, pp.worth FROM e book LEFT JOIN product_price AS pp ON e book.id = pp.book_id AND (pp.lively = 1) $choice = $context->desk(‘e book’) ->alias(‘:product_price’, ‘pp’) ->left(‘pp.lively’, 1) ->alias(‘:book_tag.tag’, ‘t’) ->the place(‘t.title LIKE’, ‘PHP’) ->left(‘pp.worth > ?’, 0) ->left(‘:book_tag.tag_id IS NOT NULL’) ->choose(‘e book.*, pp.worth’); SELECT e book.*, pp.worth FROM e book LEFT JOIN book_tag ON e 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 e book.id = pp.book_id AND (pp.lively = 1 AND pp.worth > 0) WHERE (t.title LIKE “PHP”)

LEFT JOIN s aliasem, ale vybira se z ne-aliasovane

$choice = $context ->desk(‘e book’) ->alias(‘:product_price’, ‘pp’) ->left(‘pp.lively’, 1) ->choose(‘e book.*, :product_price.worth’);//difficult SELECT e book.*, product_price.worth FROM e book LEFT JOIN product_price AS pp ON e book.id = pp.book_id AND (pp.lively = 1) LEFT JOIN product_price ON e book.id = product_price.book_id

Vyřazení řetězce z vyhledávání

Stává se, že v subselectu je třeba použít alias tabulky, a s ním pracovat. Klasická Choice se snaží tento alias dohledat, což končí chybou:

…->the place(‘creator.id = (SELECT b.author_id FROM e book AS b LIMIT 1)’);

Řešením je použití vykřičníku před !b.author_id, celé to vypadá:

$choice = $context->desk(‘creator’) ->the place(‘creator.id = (SELECT !b.author_id FROM e book AS b LIMIT 1)’); SELECT * FROM creator WHERE (creator.id = (SELECT b.author_id FROM e book AS b LIMIT 1))

Pressure index

Stejně jako v SQL: Zajistí použití požadovaného indexu.

…->forceIndex()

$choice = $context->desk(‘e book’) ->forceIndex(‘use_this_index’) ->choose(‘e book.*’); SELECT e book.* FROM e book FORCE INDEX (`use_this_index`)

Leave a comment