`Cycle\Orm\Select` adds a master table prefix before the name of the table to be joined in MtM case
Shelamkoff opened this issue · 1 comments
Shelamkoff commented
Hello.
There are two entities product and filters related by many-to-many. Needed to filter the goods through the pivot table.
The following code does everything necessary, but the generated query contains a double join, at a time when to filter it is enough to join with the pivot table.
final class ApplyProductFilters
{
public function __invoke(Select $select, array $filters): Select
{
return $select->where(static function(Select\QueryBuilder $q) use ($filters) {
foreach ($filters as $filterID => $filterValue) {
$q->orWhere(static function(Select\QueryBuilder $q) use ($filterID, $filterValue) {
$q->where('filters.@.filterId', $filterID);
if (is_array($filterValue)) {
$q->andWhere(static function(Select\QueryBuilder $q) use ($filterValue) {
if (count($filterValue) == 2 && is_numeric($filterValue[0]) && is_numeric($filterValue[1])) {
$q->orWhere('filters.@.filterValue', 'between', $filterValue[0], $filterValue[1]);
} else {
foreach ($filterValue as $value) $q->orWhere('filters.@.filterValue', $value);
}
});
} else {
$q->andWhere('filters.@.filterValue', $filterValue);
}
});
}
});
}
}
SELECT `product`.`id` AS `c0`, `product`.`name` AS `c1`, `product`.`price` AS `c2`, `product`.`attributes` AS `c3`, `product`.`description` AS `c4`, `product`.`created_at` AS `c5`,
`product`.`category_id` AS `c6`, `product`.`brand_id` AS `c7`
FROM `products` AS `product`
INNER JOIN `filters_products` AS `product_filters_pivot`
ON `product_filters_pivot`.`product_id` = `product`.`id`
INNER JOIN `filters` AS `product_filters`
ON `product_filters`.`id` = `product_filters_pivot`.`filter_id`
WHERE ((`product_filters_pivot`.`filter_id` = ? AND (`product_filters_pivot`.`filter_value` = ? OR `product_filters_pivot`.`filter_value` = ? ) )OR (`product_filters_pivot`.`filter_id` = ? AND (`product_filters_pivot`.`filter_value` BETWEEN ? AND ? ) ) )
LIMIT ? OFFSET ?
I tried calling the join directly from the Cycle\ORM\Select
instance. But this way adds a master table prefix before the name of the table to be joined.
dd($select->innerJoin('filters_products', 'fp')->on('id', 'fp.product_id')->sqlStatement());
SELECT `product`.`id` AS `c0`, `product`.`name` AS `c1`, `product`.`price` AS `c2`, `product`.`attributes` AS `c3`, `product`.`description` AS `c4`, `product`.`created_at` AS `c5`,
`product`.`category_id` AS `c6`, `product`.`brand_id` AS `c7`
FROM `products` AS `product`
INNER JOIN `product`.`filters_products` AS `fp`
ON `product`.`id` = `fp`.`product_id`
LIMIT ? OFFSET ?
Originally posted by @Shelamkoff in #397