CakePHP : How to access nested associated field?

I'm new to CakePHP.

I have a problem when getting nested field. I am not sure if other people have encountered the same problem before.

Say, I have three tables with the following filds:

User
- id
- username
- password
- registration_ip
Post
- id
- user_id
- title
- body
- posting_ip
Comments
- id
- user_id
- post_id
- body
- commenting_ip

I want to make the following query:

<?php
    ... 
    $query = $this->getTableLocator()->get("users")->find();
    $query->contain([
        "Posts",        // --> retrieve posts made by a user
        "Comments",     // --> retrieve comments made by a user

        // --> retrieve comments made to a post of a user
        "Posts.Comments" => function ($q) {
            /**
             * Here lies the problem. This is just one example, there
             * might be other examples for this. 
             *
             * I want to select comments to a user's post which IP is 
             * the same as the user's registration IP. 
             * 
             * But when I do, it creates an errors. 
             *  
             * How can multiple dots (nested associations) work in
             * CakePHP?
             * 
             * In Laravel, if I remember correctly, 
             * I was able to do something similar to this. 
             * 
             * I want to use dots to refer to nested associations. 
             * (more than one level).
             */
            return $q->where([
                "Posts.Comments.commenting_ip" => "Users.registration_ip"
            ]);
        }
    ]);

Is it possible to use these chained dots to refer to deeper associations.

A possible solution I have in mind is to create another association in the UsersTable (say CommentsToPosts). This might break the nesting of Posts and Comments though.

Is there a straightforward solution without having to create another association?

Thanks a lot! Hope this question may also be relevant to other people's question in the future.

Solution

Referencing nested associations using the dot syntax is not meant to be used in conditions, you'd simply use the target association alias there, as the passed query will always be the one that includes the associated table.

Furthermore, if you want to compare fields in key/value conditions, then you need to use expressions, for example an identifier expression, as otherwise the value side of such conditions ends up as a bound parameter.

After reading your comments I realized that you didn't make Users accessible, which is required as hasMany and belongsToMany are retrieved in separate queries, as you've noticed. You can achieve this by including the belongsTo association from Comments to Users too (if it doesn't exist, create it), that should join it into the query that retrieves the comments.

$query->contain([
    'Posts.Comments' => [
        'queryBuilder' => function (\Cake\ORM\Query $q) {
            return $q->where([
                'Comments.commenting_ip' => $q->identifier('Users.registration_ip');
            ]);
        },
        'Users',
    ],
]);