Multiple where conditions giving SQL error

[DavidOliver]

Version: 0.6.2 (Symphony 2.2.5)

I have the following in an event:

$products_query = new SymQLQuery();
$products_query
    ->select('name, brand, instances')
    ->from('products')
    ->perPage(999);
foreach ($_SESSION['sym-cart'] as $key => $item) {
    $products_query->where('instances', $key);
}
$products = SymQL::run($products_query);

The instances field is a SBL+ field.

When there is more than one product in the cart, meaning ->where is called more than once, and the event is triggered:

Symphony Fatal Database Error
Not unique table/alias: 't137'
An error occurred while attempting to execute the following query
SELECT SQL_CACHE e.id, e.section_id, e.author_id, UNIX_TIMESTAMP(e.creation_date) AS creation_date FROM sym_entries AS e LEFT JOIN sym_entries_data_137 AS t137 ON (e.id = t137.entry_id) LEFT JOIN sym_entries_data_137 AS t137 ON (e.id = t137.entry_id) WHERE 1 AND e.section_id = '1' AND t137.relation_id IN ('1723') AND t137.relation_id IN ('3096') ORDER BY e.idDESC LIMIT 0, 999

The t137 alias is created more than once.

When ->where is called multiple times using system:id, this error doesn't occur.

[nickdunn]

Hmm yes, this is most likely more of a Symphony bug, I think. It will try to join the SBL table multiple times, which is unnecessary. You should create an array of the keys you want to filter on, and set the where once:

$keys = array();
foreach ($_SESSION['sym-cart'] as $key => $item) {
    $keys[] = $key;
}
$products_query->where('instances', implode(', ', $keys));

I think this will work as Symphony will take the comma delimited string of IDs and converts it to an array.

[DavidOliver]

Nick, I see - thanks.

I'm now getting product entry objects, but they're the wrong ones. Instead of the two products that contain the instances given, I'm getting three products that have nothing to do with the instances. (My "instances" are versions of products.)

I've checked that the implode is giving the right instance ids.

[DavidOliver]

Fix from ChriZ?

[nitriques]

@DavidOliver Pull Request ? :P