Hi, i have two user_id’s in a table called ‘Peoples’ and i want to output the data of the second one that is not declared as ‘user_id’ but as ‘user2id’
how can i do it?
Right now i just do:
$user = $this->Users->get($id, [
‘contain’ => [‘Profiles’],
]);
I guess i need to do a LeftJoin with the ‘Peoples’ table and the ‘user2id’ but i dont know how…
Best,
should be like:
$query = $users->find()
->join([
‘table’ => ‘peoples’,
‘alias’ => ‘f’,
‘type’ => ‘LEFT’,
‘conditions’ => ‘f.frequestid = Users.id’,
]);
but included into the:
$user = $this->Users->get($id, [
‘contain’ => [‘Peoples’’],
]);
But i dont know how to use the above statements with the below ones…
It should just be a matter of creating a new association on your Users table.
no i think that you misunterstood me…
both tables are already linked by the user_id. i want to access the data of another user and i want to identify him by the ‘user2id’ because there is already an object with user_id… best would be if i can add the user2id to this object with a leftjoin…
users table is also higher than peoples
It sounds like you have two fields in your Peoples table which are both IDs that index into your Users table, but for different purposes? For example, maybe one is the user’s profile and the other is the user’s manager or something like that. In which case you would need a second association, as I said (though I guess on the Peoples table, not Users), and then you’d contain ['Profiles', 'Managers'] or whatever.
If that’s not your scenario, I think you may need to show some database schema and table associations, because this isn’t clear. You’ve referenced “user2id” but also “frequestid”, for example.
Table Peoples:
CREATE TABLE peoples (
id int(11) NOT NULL,
user_id int(11) NOT NULL,
peopleid int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
UsersTable:
$this->hasMany(‘Peoples’);
UsersController:
$user = $this->Users->get($id, [
‘contain’ => [‘Profiles’,‘Peoples’’],
]);
View:
<?php foreach ($user->peoples as $key => $people) { ?> <div class="content">
<?php
echo $people->peopleid;
?>
</div>
<br>
<?php
}
?>
what i want:
echo $people->peopleid->$user->$profile->$name
I’m more confused than ever. Your peoples table has just those three fields? What is peopleid a reference to?
it was a new beginning for you.
it is a request for friendship. the user_id came from the users object after been added to the peoples table and it is the id in the users table (primarykey). the peopleid is the id of the user that i want to add to the friendlist or who i want to approve.
i want to show the name of the user behind the ‘peopleid’ but i cant use it because i dont know how…
" echo $user->$people-> peopleid ->$profile->$name "
should be something like that…
the user object should be extended by the user behind the peopleid
Okay, so the structure is actually that users can “have and belong to” many users, as a friend relation, and the peoples table is the join table for this structure?
yes but without the belongsto so far… i dont know if i will need that somewhere in the future…
what i meant with friendlist is the peoples table…
Okay, so what you really want to do here is something like this (untested):
UsersTable:
$this->belongsToMany('Friends', [
'className' => 'Users',
'joinTable' => 'peoples',
'foreignKey' => 'user_id',
'targetForeignKey' => 'peopleid',
]);
UsersController:
$user = $this->Users->get($id,
'contain' => ['Friends'],
);
View:
foreach ($user->friends as $friend) {
echo $friend->name;
}
its not working:
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Users.user_id’ in ‘on clause’
the peopleid of the friends table should become equal to the user_id of the peoples table… in my scenario it should also be ‘hasOne’ not ‘BelongsTo’ … every peopleid has only one user
how can i define a association of an address table of which the friends table ‘hasOne’ by the way?
SQL Query:
SELECT Users.id AS Users__id, Users.email AS Users__email, Users.password AS Users__password, Users.role AS Users__role, Users.image AS Users__image, Users.created AS Users__created, Users.modified AS Users__modified, Friends.id AS Friends__id, Friends.email AS Friends__email, Friends.password AS Friends__password, Friends.role AS Friends__role, Friends.image AS Friends__image, Friends.created AS Friends__created, Friends.modified AS Friends__modified FROM users Users LEFT JOIN users Friends ON Friends.id = (Users.user_id) WHERE Users.id = :c0 LIMIT 1
Well, I did say it was untested. 
Just 'foreign_key' => 'id', it would seem.
It’s a belongsToMany relation because people can have many friends, and be the friend of many people. Don’t think about the relation to the join table in cases like that; that’s almost always something you can ignore entirely.
it works without any errors but now the friend is the same as the user and thats not correct:
user’ => object(App\Model\Entity\User) id:0 {
‘id’ => (int) 8
‘created’ => object(Cake\I18n\FrozenTime) id:1 { }
‘modified’ => object(Cake\I18n\FrozenTime) id:2 { }
‘friend’ => object(App\Model\Entity\User) id:3 {
‘id’ => (int) 8
‘created’ => object(Cake\I18n\FrozenTime) id:4 { }
‘modified’ => object(Cake\I18n\FrozenTime) id:5 { }
‘[new]’ => false
‘[accessible]’ => [ ]
‘[dirty]’ => [ ]
‘[original]’ => [ ]
‘[virtual]’ => [ ]
‘[hasErrors]’ => false
‘[errors]’ => [ ]
‘[invalid]’ => [ ]
‘[repository]’ => ‘Friends’
}
the database entry is:
INSERT INTO peoples (id, user_id, peopleid) VALUES
(35, 8, 1);
so the friend in the list should be the one with peopleid 1
Here’s an example from my own code, which works fine in the Articles table:
$this->belongsToMany('Categories', [
'className' => 'Categories',
'joinTable' => 'articles_categories',
'foreignKey' => 'article_id',
'targetForeignKey' => 'category_id',
]);
In your case, you want the relation to be called Friends, not Categories. You want those friends to be Users, not Categories. You want the join table to be peoples, not articles_categories. And foreignKey and targetForeignKey should be the fields from the join table, you should be able to see which one relates to the record that you’re finding associated record for (article_id in my case, should be user_id in yours, right?) and which relates to the records in the associated table (category_id in my case, peopleid in yours).
So, it really seems that it should be:
$this->belongsToMany('Friends', [
'className' => 'Users',
'joinTable' => 'peoples',
'foreignKey' => 'user_id',
'targetForeignKey' => 'peopleId',
]);
which is what I had the first time. You didn’t change it from belongsToMany to some other association, did you?
not… i used your code now and there was no error but there is still the same issue… he did not create a user that is based on the peopleid…
friend is just the same user as declared in the peoples table no exchange between the ids…
peopleid should become user_id
if I use ‘className’ => ‘Users’ he is using the table Users instead of the table peoples so everything is wrong…
SELECT Peoples.id AS Friends_CJoin__id, Peoples.user_id AS Friends_CJoin__user_id, Peoples.frequestid AS Friends_CJoin__frequestid, Friends.id AS Friends__id, Friends.email AS Friends__email, Friends.created AS Friends__created, Friends.modified AS Friends__modified FROM users Friends INNER JOIN Peoples Peoples ON Friends.id = (Peoples.user_id) WHERE Peoples.user_id in (8)
if i use ‘className’ => ‘Peoples’ i get the correct table…
SELECT Peoples.id AS Friends_CJoin__id, Peoples.user_id AS Friends_CJoin__user_id, Peoples.frequestid AS Friends_CJoin__frequestid, Friends.id AS Friends__id, Friends.user_id AS Friends__user_id, Friends.frequestid AS Friends__frequestid FROM Peoples Friends INNER JOIN Peoples Peoples ON Friends.id = (Peoples.peopleid) WHERE Peoples.user_id in (8)
but this is still not the solution because i need the information of the user behind the peopleid…
like this he only provides the information of regular user_id