Is it possible to reduce the number of joins when accessing the pivot table?

[Shelamkoff]

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('[email protected]', $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('[email protected]', 'between', $filterValue[0], $filterValue[1]);
                            } else {
                                foreach ($filterValue as $value) $q->orWhere('[email protected]', $value);
                            }
                        });
                    } else {
                        $q->andWhere('[email protected]', $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 ?

[roxblnfk]

You can make yet another relation like product HasMany product_filters_pivot and use it directly.
BTW your question looks like a good issue for the ManyToMany relation.

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.

Looks like a bug. I need to play with that

[Shelamkoff]

You can make yet another relation like product HasMany product_filters_pivot and use it directly.

I thought about it, but in my opinion it is a crutch.

[roxblnfk]

You can declare product_filters_pivot BelongsTo product an use it from other side :)

[roxblnfk]

By the way, calculating the uselessness of a parasitic join is very difficult. Semantically all joins right now are correct, even if you don't use the sampling conditions directly for the filters entity.