cycle/orm

`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

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