Hello everybody,
We are a little stuck here with a specific VisionCart hack that we are trying to achieve. In a nutshell: we are trying to create a filter system. We've been struggling with this for a moment now. So I turned to the pros.
Essentially, during the XPDO query, we need to check that the product in question has the correct amount of (selected) categories. Works fine with with SQL like this:
SELECT ".$vcProduct.".*, ".$vcProductCategory.".* FROM ".$vcProduct."
INNER JOIN ".$vcProductCategory." ON ".$vcProduct.".id = ".$vcProductCategory.".productid
WHERE ".$vcProductCategory.".categoryid IN (".$filterId.")
GROUP BY ".$vcProductCategory.".productid
HAVING (COUNT(".$vcProductCategory.".categoryid) = ".count(explode(',', $filterId)).")", array());
However, we can't use the SQL version because VC stumbles over another issue later on. So we tried to achieve the same effect with XPDO:
$query = $this->modx->newQuery('vcProduct');
$query->innerJoin('vcProductCategory', 'ProductCategory', 'ProductCategory.productid = vcProduct.id');
$query->select(array(
'ProductCategory.*',
'vcProduct.*'
));
$where = array(
'ProductCategory.categoryid:IN' => explode(',', $filterId)
);
$query->where($where, xPDOQuery::SQL_AND);
$query->groupby('ProductCategory.productid');
$query->having(array(
'count(ProductCategory.categoryid)' => count(explode(',', $filterId))
));
Resulting invalid SQL code (with toSQL()):
SELECT ProductCategory.*, vcProduct.* FROM `modx_visioncart_products` AS `vcProduct` JOIN `modx_visioncart_products_categories` `ProductCategory` ON ProductCategory.productid = vcProduct.id WHERE ( `ProductCategory`.`categoryid` IN ('32','20') ) GROUP BY ProductCategory.productid HAVING `count(ProductCategory`.`categoryid)` = '2'
Also tried
$query = $this->modx->newQuery('vcProduct');
$query->innerJoin('vcProductCategory', 'ProductCategory', 'ProductCategory.productid = vcProduct.id');
$query->select(array(
'ProductCategory.*',
'vcProduct.*'
));
$where = array(
'ProductCategory.categoryid:IN' => explode(',', $filterId)
);
$query->where($where, xPDOQuery::SQL_AND);
$query->groupby('ProductCategory.productid');
$query->having(array(
count(ProductCategory.categoryid) => count(explode(',', $filterId))
));
Which just generally doesn't result in anything good:
SELECT ProductCategory.*, vcProduct.* FROM `modx_visioncart_products` AS `vcProduct` JOIN `modx_visioncart_products_categories` `ProductCategory` ON ProductCategory.productid = vcProduct.id WHERE ( `ProductCategory`.`categoryid` IN ('32','20') ) GROUP BY ProductCategory.productid HAVING
We tried some other combinations like putting the count in the where clause, etc. Nothing came of it.
How can we achieve what we want to do?
[ed. note: elgrandeadi last edited this post 13 years, 9 months ago.]
☆ A M B ☆
MODX Staff