How can I return data found from a contain and left joins in a single object/array in CakePHP 3?

Question

I have 3 tables set up in my database so far:

Users / User_Profiles / Addresses

The addresses table is used for storing address both from users and eventually from companies and such in the future. The User_Profiles table has more information about a user that doesn't involve addresses.

Users Table:

id | username | password | etc....


User_Profiles:

id | user_id | phone | website | bio | address_id | shipping_address_id


Addresses

id | address | city | state | zip | etc...

Here's my issue. I want to find a user by id, then return all of his information - his info from user_profiles and his info from addresses (both address_id and shipping_address_id point to the addresses table).

In my model I currently have this:

 public function getUserInfo($user_id){

     return $this->find()
        ->contain(['user_profiles'=>['fields'=>['user_id','phone_number', 'website', 'bio', ]]])
        ->where(['id' => $user_id])
        ->first();
 }

This is simple enough and returns my user_profile information. Now I need to join the addresses table (twice, I guess?) for getting the shipping address and the regular address (by shipping_address_id and address_id).

How can I do this? I figured I could use TableRegistry to load in the user_profiles table to store the address_id and shipping_address_id as variables, and then work my way from there, but it seems overly complicated.

Ideally I'd want all of the information returned in an object - the user_profile info along with any addresses associated.

Daniel Castro Daniel Castro · Accepted Answer · 2014-12-21T19:16:42

First things first

You need to tell Cake how your Tables relate to each other.
In your UsersTable.php file, add the following method:

public function initialize(array $config)
    $this->hasOne('UserProfiles');
}

Now Cake knows how to look for information on the UserProfiles table when you retrieve something from the Users table. As long as you follow conventions, this should be enough for a simple association.

Now on your UserProfilesTable.php file, we can define slightly more complex associations.

public function initialize(array $config)

    $this->belongsTo('Users');

    $this->belongsTo('Address', [
        'className' => 'Addresses',
        'foreignKey' => 'address_id',
        'property_name' => 'address'
    ]);

    $this->belongsTo('ShippingAddress', [
        'className' => 'Addresses',
        'foreignKey' => 'shipping_address_id',
        'property_name' => 'shipping_address'
    ]);
}

The first associations is quite simple. It tells Cake that the UserProfiles table points to a record in the Users table.
As long as you follow conventions, there should be no problems here.

The next two associations need some more work.
Since you need to associate UserProfiles with Addresses twice, you have to define an alias for each association. This alias is what is going to identify your association in the contain method.

This should be enough to get your find method to work as expected.

You can learn more about associations in this section of the book.

Retrieving your data

public function getUserInfo($user_id){
    return $this->find()
        ->contain([
            'UserProfiles', 'UserProfiles.Address', 'UserProfiles.ShippingAddress'
        ])
        ->where(['id' => $user_id])
        ->first();
}

Now all the informations about Users, UserProfiles, Address and ShippingAddress will be retrieved using the find method only once.
You can also pass parameter and define conditions to each association as explained in this part of the book.

Hopefully this can help some people that face this same problem in the future.

How can I return data found from a contain and left joins in a single object/array in CakePHP 3?

2 Answers

First things first

Retrieving your data