How to query data that is associated with a specific join table entry?

Question

I have 2 tables with has many relationship: Users and Tests, an the join table generated with bake that associates both TestsUsers.

In TestsUsers, I have 2 extra fields (besides user_id and test_id) named score and date. For adding a record for the first time, I manually crafted the entity in TestsController (creating a $test entity adding test.id, user.id, and user._joinData) and used link() for saving the data.

I'm having trouble in retrieving that record for editing. So far, in the index view (shows all records and has edit and delete actions), I'm passing 3 parameters to the edit function: test_id, user_id and TestsUsers_id.

I've used:

$this->Tests->get(($test_id), [
    'contain' => 'Users',
    function ($q) use ($user_id) {
        return $q->where(['Users.id' => $user_id])  
    }
]])

and the result is:

test
    (test info)
    users
        [0]
           user_id=1
           (user info)
           _joinData
                   id=1
        [1]
           user_id=1
           (user info)
           _joinData
                    id=2

The question is, how do I access _joinData level in get() for using its id.

P.S. = I'm not allowed to load or use the TestsUsers model. Thanks

What do you mean by "access _joinData level in get()"? In the data you receive back from `get`? Or do you want to use it somehow in the call you're making *to* `get`? — Greg Schmidt, May 24 '21 at 01:47
Hi, sorry, english is not my first language, what I ment is how do i have access to that _joinData so i can compare the id in there (testsUsers_id) with the id of the record to edit (testsUsers_id). I'm giving the controller 3 parameters, user_id, test_id and testsUsers_id(_joinData), or how do I get() or find() the records from those 3 table matching those parameters without using TestsUsers table,I only manage to use only 2 parameters — Juan Manuel Gonzalez Carrascos, May 24 '21 at 12:42
In brief, I need to retrieve an existing record from the join table TestsUsers, using the ids of the user, the test and testsUsers as parameters. That would be used for updating that record — Juan Manuel Gonzalez Carrascos, May 24 '21 at 12:59
Ah, you want to get back something like what your test data is showing, but with only the one user record that has `_joinData` matching the specified ID for it? I'm actually not sure how to do that off the top of my head, but hopefully with the clarification someone will be able to help. — Greg Schmidt, May 24 '21 at 16:14
What the right way would be, would depend on whether you want to filter `Tests` too, ie if for the given `$user_id`, no join table entry exists that matches `$UsersTests_id`, should the `Tests` record with `$test_id` still be retrieved (with no `Users`), or not (the latter would result in an exception when using `get()`)? — ndm, May 25 '21 at 09:27
Yes, I want to filter by `Tests` too because I need to show `Users.name` and `Tests.name` in the edit view related to that record. I'm new to cake and programming, so, as far as I know it would be like an `inner-join` between `Users` and `Tests` adding the data on `TestsUsers`. I'm currently trying with filtering by `test_id` containing `Users` (also filtering by `user_id`) and iterating the result, saving in an array the matching `TestsUsers_id`. From there I give shape to the entity I pass to the view. I think that last approach is suboptimal, do you have other suggestion or path to follow? — Juan Manuel Gonzalez Carrascos, May 25 '21 at 16:30

score 1 · Answer 1 · answered May 26 '21 at 15:46

With your explanation in the comments, that the join table row's existence should affect whether Tests is being retrieved, you'd generally need to join with your association's tables, and apply the required conditions to filter your results.

Additionally you'd need to apply the same conditions for contain(), as that is a separate query that will not affect anything but itself, and its results will afterwards be merged into the Tests result on PHP level.

A basic example:

$test = $this->Tests
    ->find()
    ->contain('Users', function (\Cake\ORM\Query $query) use ($user_id, $TestsUsers_id) {
        return $query
            ->where([
                'Users.id' => $user_id,
                'TestsUsers.id' => $TestsUsers_id,
            ]);
    })
    // This will join in the join table as well as the target table,
    // hence conditions for the former can be applied as well
    ->innerJoinWith('Users', function (\Cake\ORM\Query $query) use ($user_id, $TestsUsers_id) {
        return $query
            ->where([
                'Users.id' => $user_id,
                'TestsUsers.id' => $TestsUsers_id,
            ]);
    })
    ->where([
        'Tests.id' => $test_id,
    ])
    // Grouping is probably not required as the uniqueness of the join
    // table's primary key should already restrict things to one row
    ->group('Tests.id')
    ->firstOrFail();

How to query data that is associated with a specific join table entry?

1 Answers1