Joins Query in Cakephp4 only returning one table data

Question

I am trying to fetch reviews using join query using CakePHP4, However the result I am getting is simple Reviews table data. How can I attach users table too.

e.g

public function getReviews()
{
    $this->request->allowMethod(['get']);
    
    //$authenticator = new JWTController();
    //$data        = $authenticator->requestAuthorization();

    $offset = 1;//$data['offset']; 
    
    $reviews = $this->getTableLocator()->get('Reviews');
    $result = $reviews
        ->find('all')
        ->join([
            'Users'=>  [
                'table'      => 'users',
                'type'       => 'INNER',
                'conditions' => 'Users.id = Reviews.userId',
            ]
        ])
        ->order(['Reviews.created' => 'DESC'])
        ->limit(25)
        ->page($offset)->toArray();
            
    $this->jsonOutput(array(
        'error' => false,
        'limit' => 25,
        'data'  => $result
    ));
}

{
    "id": 1,
    "userId": 1,
    "rating": 4,
    "review": "How are you",
    "created": "2020-10-11T23:14:44+00:00"
}

Answer 1

Inspect the generated SQL! Joining something will first and foremost only do that, create a join. If you want any data from that join, then you'd have to explicitly tell that to the query builder by selecting the respective fields:

$reviews
    ->find()
    // select all fields of the reviews table
    ->select($reviews)
    // select specific fields from the join
    ->select(['Users.username', 'Users.email', /* ... */])
    ->join(/* ... */)
    // ...

The result would look something like this (you'd probably want to use a different alias for the join, as Users is just weird, it should at least be singular):

{
    "id": 1,
    "userId": 1,
    "rating": 4,
    "review": "How are you",
    "created": "2020-10-11T23:14:44+00:00",
    "Users": {
        "username": "Foo",
        "email": "[email protected]",
        // ...
    }
}

However you should probably instead go for associations, and then use contain(), so that you don't have to repeat yourself over and over again. In your ReviewsTable class set up a BelongsTo association:

public function initialize(array $config): void
{
    // ....

    $this
        ->belongsTo('Users')
        ->setForeignKey('userId')
        ->setJoinType(\Cake\Database\Query::JOIN_TYPE_INNER);
}

Ideally you should follow the naming conventions and name the foreign key field user_id instead, then CakePHP will automatically pick it up, and you don't have to configure things with your unconventional field name (for BelongsTo the default name is the singular, lowercased/underscored variant of the association name, with _id appended).

$reviews
    ->find()
    ->contain('Users')
    ->order(/*...*/)
    // ...

The result would look something like this:

{
    "id": 1,
    "userId": 1,
    "rating": 4,
    "review": "How are you",
    "created": "2020-10-11T23:14:44+00:00",
    "user": {
        "id": 1,
        "username": "Foo",
        // ...
    }
}

See also

• Cookbook > Database Access & ORM > Associations - Linking Tables Together
• Cookbook > Database Access & ORM > Query Builder > Loading Associations
• Cookbook > CakePHP at a Glance > CakePHP Conventions > Database Conventions