Data Associations

Need Help
1

How do I turn this into a cake query? I have researched for a week looked at every option and I just cant find anything but Im thinking its possible. Is it? If so, how?

SELECT *
FROM
users Users
left JOIN rsmlinks rsmlinks ON Users.region_id = rsmlinks.region_id
left JOIN users rsmuser ON rsmuser.id = rsmlinks.user_id
WHERE
Users.id = 75

When I use a simple contain it tried to look up the Users.id = 75 for all of the tables but this isnt right it should take the region_id from the user found then look at the rsmlinks table’s region_id and then return the user_id column that then selects that user out of the users table. So the users table is being aliased but dipped twice.

2

First, you’ll need to build a model (this can be done using bake in the CLI).
After you have done that, your code should look something like:

$this->Users->find()->leftJoinWith('rsmlinks')->where(['id'=>1]);

You should look up the exacts yourself with help of the docs, I only gave the example how I would write it out of my head (so it might not work as intended)

3

Thanks I will do that. I have been wracking my brain trying to come up with a solution and not have to hand code the whole SQL query.

Brian

4

CakePHP has a beautiful ORM that allows you to do a lot without actually writing the whole SQL queury.
It allows you to use a SQL query if you really want to, but you can do pretty much everything without writing SQL.

5

Ya am loving cake. Its allowed me to create an app that would have taken a team over a year in just a few months. I am still having the same issue. I feel it might be an issue with linking. If I had a separate “users” table I dont think this would be an issue but its still matching on the users.id field. I am going to have to hand code the SQL for this. I just dont see a way around it. Thanks for your help.

6

Isn’t that what you wanted? since WHERE Users.id = 75?

7

Yes partially. This is what I ended up doing by converting everything to an array and embed it.

$userMgr = $this->Users->find()
->select([‘rsmuser.name’, ‘rsmlinks.created’, ‘rsmlinks.modified’])
->join([
‘rsmlinks’ => [
‘table’ => ‘rsmlinks’,
‘type’ => ‘INNER’,
‘conditions’ => ‘Users.region_id = rsmlinks.region_id’,
],
‘rsmuser’ => [
‘table’ => ‘users’,
‘type’ => ‘INNER’,
‘conditions’ => ‘rsmuser.id = rsmlinks.user_id’,
]
])
->where([‘Users.id’ => $id])
->toArray();

8

The problem here probably lies in a non-convention-conform table- and column-naming. And as a result of this you get wrong table associations when you bake your models.

The tables and names that cause problems are: “rsmuser” and “users”.

You have two tables with users that’s creating problems, because “rsmlinks.user_id” per convention points to the “users” table and the column “id” and NOT to the table “rsmuser” and the column “id”.

That’s probably also the reason, why the table “users” get’s dipped twice.

There are two solutions to make the ORM work properly ( = fix your problem):

  1. rename your tables and columns according to the convention and fix your table-associations
  2. fix your table-associations according to your current database-layout

That way you should get the ORM working as expected. The solution you posted is only a hacky fix, because table associations are probably still wrong.

Keep in mind that you need to follow the convention or set your associations properly to make the ORM work properly.

These are the needed parts from the documentation:
Database conventions: https://book.cakephp.org/3.0/en/intro/conventions.html#database-conventions
Model conventions: https://book.cakephp.org/3.0/en/intro/conventions.html#model-conventions
Linking Tables: https://book.cakephp.org/3.0/en/orm/associations.html

Another tip regarding db-design:
It’s also good practice to keep contact-data and user-data in separate tables. In the other thread you mixed them both.