Change alias of belongsToMany bridge table

Need Help
1

Hi all,

i have a table Products thats belongsToMany Labels of different types (tags, categories).

Given a product, I want to find products that share the max amount of tags but that are of different categories.
So I want to find the products that are associated with a list of given tags but that are not associated with a list of given categories.

I built a query thats finds Products that both matching and notMatching Labels. To do so, I created two associations on-the-fly:

$this->Products->belongsToMany('Tags', [
    'className' => 'Labels',
    'foreignKey' => 'product_id',
    'targetForeignKey' => 'label_id',
    'joinTable' => 'labels_products',
]);
$this->Products->belongsToMany('Categories', [
    'className' => 'Labels',
    'foreignKey' => 'product_id',
    'targetForeignKey' => 'label_id',
    'joinTable' => 'labels_products',
]);

But when i run the query below:

$product_id = $this->request->query('product_id');
$related_products = $this->Products->find()
    ->select([
        'count' => 'COUNT(LabelsProducts.id)',
    ])
    ->select($this->Products);
    ->where([
        'Products.id !=' => $product_id,
    ])
    ->matching('Tags', function ($q) use ($product_id) {
        return $q
            ->where([
                'Tags.id IN' => $this->Products->Tags->find()
                    ->select([
                        'Tags.id',
                    ])
                    ->where([
                        'Tags.type' => 'tag',
                    ])
                    ->innerJoinWith('Products', function ($p) use ($product_id) {
                        return $p
                            ->where([
                                'Products.id' => $product_id,
                            ]);
                    });
            ]);
    })
    ->notMatching('Categories', function ($q) use ($product_id) {
        return $q
            ->where([
                'Categories.id IN' => $this->Products->Categories->find()
                    ->select([
                        'Categories.id',
                    ])
                    ->where([
                        'Categories.type' => 'category',
                    ])
                    ->innerJoinWith('Products', function ($p) use ($product_id) {
                        return $p
                            ->where([
                                'Products.id' => $product_id,
                            ]);
                    });
            ]);
    })
    ->group('Products.id')
    ->limit(4)
    ->order([
        'count' => 'DESC',
    ]);

Cake uses the same alias for both bridge table (LabelsProducts), so it returns me wrong results.
Is it possible to change the bridge alias for belongsToMany association?

2

You should try to add the conditions key to your association definition.

Otherwise I think I would use only matching adding a condition where categories are filtered out.

3

Hi and thanks for your response,

adding conditions in the association doesn’t solve the alias problem for bridge table…

Also use only the matching statement where categories are filtered out isn’t correct, because i want the query to return products that are not associated with any category

4

Ah ok. Than it may be a case when you should use just a raw sql statement and execute it.

5

Should I open an issue in Github?

6

Never mind, there is already an issue for that:

h ttps://github.com/cakephp/cakephp/issues/9499

Thank you