Data Results in controller

Need Help
1

So I have the following code…

$user = $this->Users->get($id, [
‘contain’ => [‘Regions’, ‘Perms’, ‘Usertypes’, ‘Roles’, ‘Assets’, ‘Accmemos’, ‘Rsmlinks’, ‘Tclinks’, ‘Tclinks.Users’, ‘Tclinks.Regions’, ‘Rsmlinks.Users’, ‘Rsmlinks.Regions’]
]);

It pulls back data correctly except when we get to RSMLinks and TCLinks… Its pulling back based on the users user_id column. I would like those 2 tables to pull back data based on another column in the users table. How do I accomplish this?

Brian

2

I would like those 2 tables to pull back data based on another column in the users table. How do I accomplish this?

by removing the RSMLinks and TCLinks Tables from the “contain”-array and adding

->innerJoin(table, condition)

See Query Builder - 3.10

IDK if this is possible only using “contain”.

3

Hey thanks for the info. This is what I was looking for in the query builder documentation but couldnt make heads/tails from it. Im not concerned by doing this in the contain array but just having the ability to do it without manually scripting the SQL query.

b

4

To add to this Ive made a bit of headroom kinda. . Im getting this built by cakephp

SELECT
Rsmlinks.id AS Rsmlinks__id,
Rsmlinks.user_id AS Rsmlinks__user_id,
Rsmlinks.region_id AS Rsmlinks__region_id,
Rsmlinks.created AS Rsmlinks__created,
Rsmlinks.modified AS Rsmlinks__modified,
Users.id AS Users__id,
Users.email AS Users__email,
Users.code1 AS Users__code1,
Users.name AS Users__name,
Users.region_id AS Users__region_id,
Users.company AS Users__company,
Users.perm_id AS Users__perm_id,
Users.usertype_id AS Users__usertype_id,
Users.onecoreApproved AS Users__onecoreApproved,
Users.vdsApproved AS Users__vdsApproved,
Users.role_id AS Users__role_id,
Users.notificationtype AS Users__notificationtype,
Users.mobilePhone AS Users__mobilePhone,
Users.otherPhone AS Users__otherPhone,
Users.address AS Users__address,
Users.address2 AS Users__address2,
Users.city AS Users__city,
Users.state AS Users__state,
Users.zip AS Users__zip,
Users.created AS Users__created,
Users.modified AS Users__modified,
Users.webbrowser AS Users__webbrowser
FROM
rsmlinks Rsmlinks
INNER JOIN users Users ON (
Rsmlinks.region_id = ‘Users.region_id’
AND Users.id = (Rsmlinks.user_id)
)
WHERE
Rsmlinks.user_id in (75)

When I need this:

SELECT
Rsmlinks.id AS Rsmlinks__id,
Rsmlinks.user_id AS Rsmlinks__user_id,
Rsmlinks.region_id AS Rsmlinks__region_id,
Rsmlinks.created AS Rsmlinks__created,
Rsmlinks.modified AS Rsmlinks__modified,
Users.id AS Users__id,
Users.email AS Users__email,
Users.code1 AS Users__code1,
Users.name AS Users__name,
Users.region_id AS Users__region_id,
Users.company AS Users__company,
Users.perm_id AS Users__perm_id,
Users.usertype_id AS Users__usertype_id,
Users.onecoreApproved AS Users__onecoreApproved,
Users.vdsApproved AS Users__vdsApproved,
Users.role_id AS Users__role_id,
Users.notificationtype AS Users__notificationtype,
Users.mobilePhone AS Users__mobilePhone,
Users.otherPhone AS Users__otherPhone,
Users.address AS Users__address,
Users.address2 AS Users__address2,
Users.city AS Users__city,
Users.state AS Users__state,
Users.zip AS Users__zip,
Users.created AS Users__created,
Users.modified AS Users__modified,
Users.webbrowser AS Users__webbrowser
FROM
rsmlinks Rsmlinks
INNER JOIN users Users ON (
Rsmlinks.user_id = users.id
)
WHERE
Rsmlinks.region_id in (1)

My code is this:

->contain([‘Rsmlinks.Users’ => function ($q) {
return $q->where([‘Rsmlinks.region_id’ => ‘Users.region_id’]);
}])