Cakephp3.1: Using matching() and notMatching() on the same associated model at once

Question

I want do implement a search function for recipes and their associated ingredients. The user should specify ingredients that he wants to exclude from the search and at the same time ingredients that are contained in the recipes he is looking for.

Those are my two finders:

public function findByContainingIngredients(Query $query, array $params)
{
    $ingredients = preg_replace('/\s+/', '', $params['containing_ingredients']);

    if($ingredients) {
        $ingredients = explode(',', $ingredients);
        $query->distinct(['Recipes.id']);
        $query->matching('Ingredients', function ($query) use($ingredients) {
            return $query->where(function ($exp, $query) use($ingredients) {
                return $exp->in('Ingredients.title', $ingredients);
            });
        });
    }
    return $query;
}


public function findByExcludingIngredients(Query $query, array $params)
{
    $ingredients = preg_replace('/\s+/', '', $params['excluding_ingredients']);

    if($ingredients) {
        $ingredients = explode(',', $ingredients);
        $query->distinct(['Recipes.id']);
        $query->notMatching('Ingredients', function ($query) use ($ingredients) {
            return $query->where(function ($exp, $query) use ($ingredients) {
                return $exp->in('Ingredients.title', $ingredients);
            });
        });

    }
    return $query;
}

In the controller I call:

            $recipes = $this->Recipes->find()
            ->find('byExcludingIngredients', $this->request->data)
            ->find('byContainingIngredients', $this->request->data);

If the user excludes an ingredient from the search and specifies one ore more ingredient that he wants to include, there are zero results. When I take a look at the generated SQL I see the problem:

SELECT 
  Recipes.id AS `Recipes__id`, 
  Recipes.title AS `Recipes__title`,
  .....

FROM 
  recipes Recipes 
  INNER JOIN ingredients Ingredients ON (
    Ingredients.title IN (: c0) 
    AND Ingredients.title IN (: c1) 
    AND Recipes.id = (Ingredients.recipe_id)
  ) 
WHERE 
  (
    Recipes.title like '%%' 
    AND (Ingredients.id) IS NULL
  ) 
GROUP BY 
  Recipes.id, 
  Recipes.id

The problem is "AND (Ingredients.id) IS NULL". This line makes the results from the including ingredients disappear. My approaches:

Creating an alias when calling notMatching() on the association twice. I think this is not possible in Cake3.1
Using a left join on the PK/FK and the excluded title and creating an alias. Basically writing my own notMatching function. This works, but it does not feel right.

Are there other solutions?

score 5 · Accepted Answer · answered Sep 18 '17 at 14:12

To anybody coming to this page and concluding you cannot combine a matching() and notMatching() on the same associated class:

Yes, it is possible (as of Cake 3.4.9 anyway) to do such a find. But you have to use a different alias for the target table - that is an alias that is different to the usual class name.

So in OP's situation, you would put this in RecipesTable.php :

public function initialize(array $config) {
    ... usual stuff

    $this->belongsToMany('Ingredients', [
        'foreignKey' => 'recipe_id',
        'targetForeignKey' => 'ingredient_id',
        'joinTable' => 'ingredients_recipes'
    ]);
    // the next association uses an alias,
    // but is otherwise *exactly* the same as the previous assoc.
    $this->belongsToMany('ExcludedIngredients', [
        'className' => 'Ingredients',
        'foreignKey' => 'recipe_id',
        'targetForeignKey' => 'ingredient_id',
        'joinTable' => 'ingredients_recipes'
    ]);
}

And you should be able to write a find statement like this:

$this->find()
    -> ... usual stuff
    ->matching('Ingredients',function($q) use($okIngredients) {
        ... check for ingredients ...
    })
    ->notMatching('ExcludedIngredients', function($q) use($excludedIngredients) {
        ... check for ingredients ...
    });

This does work. Unfortunately, when I used it in an analogous situation with thousands of rows in my 'Recipes' table the query took 40 seconds to run. So I had to go back and replace the notMatching() by a hand-crafted join anyway.

score 1 · Answer 2 · answered Dec 16 '16 at 06:16

1

I think what you could do is manually join ingridients table once more with different alias (http://book.cakephp.org/3.0/en/orm/query-builder.html#adding-joins) and then use it in matching/notMatching

answered Dec 16 '16 at 06:16

Andrej Gr

56
2

1

The matching/notMatching functions expect the first parameter to be the name of an assoiciation, not a table name or an alias. Sadly, this does not work. – napolin Dec 20 '16 at 13:41

Cakephp3.1: Using matching() and notMatching() on the same associated model at once

2 Answers2