Custom finder in table with subquery

1

I have a query that shows all publicly visible recipes and I want to extend this query with a query that shows recipes from the users that the logged in user follows.

This works in the controller, but I would like to move the query to the table.
However, I fail because of the following error message (from the subquery):

Undefined property Recipes. You have not defined the Recipes association on App\Model\Table\RecipesTable.

and don’t know how to solve this.
Any tip or link to a place in the documentation would be helpful.

This is the code from the custom Finder in the table, that throws the error (the “subquery” part needs to be changed)

    public function findVisibleRecipes(SelectQuery $query, User $user) 
    {
        debug($user);
        $query = $query->where(['visibility_id' => 3,'recipestate_id'=>2]);
        $subquery = $this->Recipes->Users->Userrelationships
            ->find()
            ->select(['user_id'])
            ->where(function (QueryExpression $exp, SelectQuery $q) {
            return $exp->equalFields('Recipes.user_id', 'Userrelationships.user_id');
            })
            ->andWhere(['Userrelationships.follower' => $user->id,'Userrelationships.userrelationshipstate_id'=>2]);

        $query->where(
            [
                'OR' => [
                    $query->clause('where'),
                    ['Recipes.visibility_id'=>2,'Recipes.recipestate_id'=>2,'Recipes.user_id IN'=>$subquery]
                ]
            ],
            [], 
            true
        );
    
        debug($query);
        die();
        return $query;
    }

This is the working code from the controller:

if (isset($this->request->getAttribute('identity')->id)) {
            $user = $this->fetchTable('Users')
                ->find()
                ->where(['Users.id' => $this->request->getAttribute('identity')->id])
                ->select(['Users.id'])
                ->first();
            
            //$query = $this->Recipes->find('visibleRecipes',$user);
            //die();

            //Find all recipes that visibility_id == 3 (viewable for not logged in viewers) and recipestate_id == 2 (checked and approved by an editor)
            $query = $this->Recipes
                ->find()
                ->where(['visibility_id' => 3,'recipestate_id'=>2]);

            //Subquery to find the user_ids of the users that the current logged in user followes
            $subquery = $this->Recipes->Users->Userrelationships
                ->find()
                ->select(['user_id'])
                ->where(function (QueryExpression $exp, SelectQuery $q) {
                    return $exp->equalFields('Recipes.user_id', 'Userrelationships.user_id');
                })
                ->andWhere(['Userrelationships.follower' => $user->id,'Userrelationships.userrelationshipstate_id'=>2]);
            
            //Find all recipes of the users that the current logged in user follows
            $query->where(
                [
                    'OR' => [
                        $query->clause('where'),
                        ['Recipes.visibility_id'=>2,'Recipes.recipestate_id'=>2,'Recipes.user_id IN'=>$subquery]
                    ]
                ],
                [], 
                true
            );
            //Find all Recipes of the current logged in user, that are visible only for the creator)
            $query->where(
                [
                    'OR' => [
                        $query->clause('where'),
                        ['Recipes.visibility_id'=>1,'Recipes.recipestate_id'=>2,'Recipes.user_id'=>$user->id]
                    ]
                ],
                [], 
                true
            );
        }
        else {
            $query = $this->Recipes->find('publicRecipes');
        }
2

I think I found the solution:
I added in my table method:

$users = $this->getAssociation('Users');

and changed the subquery to

        $subquery = $users->Userrelationships
            ->find()
            ->select(['user_id'])
            ->where(function (QueryExpression $exp, SelectQuery $q) {
            return $exp->equalFields('Recipes.user_id', 'Userrelationships.user_id');
            })
            ->andWhere(['Userrelationships.follower' => $user->id,'Userrelationships.userrelationshipstate_id'=>2]);

so finally the custom finder looks like this:

    public function findVisibleRecipes(SelectQuery $query, User $user) 
    {
        //Find all recipes that visibility_id == 3 (viewable for not logged in viewers) and recipestate_id == 2 (checked and approved by an editor)
        $query = $query->where(['visibility_id' => 3,'recipestate_id'=>2]);
        
        //get the Association between recipes and users
        $users = $this->getAssociation('Users');
        
        //Subquery to find the user_ids of the users that the current logged in user followes
        $subquery = $users->Userrelationships
            ->find()
            ->select(['user_id'])
            ->where(function (QueryExpression $exp, SelectQuery $q) {
            return $exp->equalFields('Recipes.user_id', 'Userrelationships.user_id');
            })
            ->andWhere(['Userrelationships.follower' => $user->id,'Userrelationships.userrelationshipstate_id'=>2]);
        
        //Find all recipes of the users that the current logged in user follows (depending on the subquery)
        $query->where(
            [
                'OR' => [
                    $query->clause('where'),
                    ['Recipes.visibility_id'=>2,'Recipes.recipestate_id'=>2,'Recipes.user_id IN'=>$subquery]
                ]
            ],
            [], 
            true
        );

        //Find all Recipes of the current logged in user, that are visible only for the creator (==current logged in user))
        $query->where(
            [
                'OR' => [
                    $query->clause('where'),
                    ['Recipes.visibility_id'=>1,'Recipes.recipestate_id'=>2,'Recipes.user_id'=>$user->id]
                ]
            ],
            [], 
            true
        );
    
        return $query;
    }

seems to work:-)

3

In your controller function, $this->Recipes is the RecipesTable object, and $this->Recipes->Users is the association from there to the UsersTable. In your RecipesTable function, the RecipesTable object is simply $this, so the association to the UsersTable is at $this->Users instead of $this->Recipes->Users.

1 Like