How can I find all entries for a model without making a long list of OR conditions?

Question

I'm having trouble compiling CakePHP find (), which returns the records I'm looking for.

My associations are as follows:

User → (there are many) → Friends,
User → (there are many) → Messages

I am trying to display a list of all recent messages from friends of friends, in other words, to list each message that was created by a friend of the current user who is logged in.

The only way I can do this is to put all user_users user_ids into a large array, and then going through each one so that the find () call looks something like this:

$posts = $this->Post->find('all',array( 'conditions' => array( 'Post.user_id' => array( 'OR' => array( $user_id_array[0],$user_id_array[1],$user_id_array[2] # .. etc ) ) ) )); 

I get the impression that this is not the best way to do things, as if this user was popular in that there are a lot of OR conditions. Can anyone suggest a better alternative?

To clarify, here is a simplified version of my database:

Users table
identifier
Username
etc.

Friends table
identifier
user_id
friend_id
etc.

Messages table
identifier
user_id
etc.

Answer 1

After reviewing what you rewrote, I think I understand what you are doing. Your current structure will not work. POSTS has no friends links. Therefore, based on the pattern you posted, friends CANNOT add POSTS. I think that you are trying to link to a friend as one of the other users. Meaning, FRIEND users are actually just another USER in the USERS table. This is a HABTM standalone link. So here is what I would suggest:

1- First, make sure the HABTM table is created in the database:

- MySQL CREATE TABLE users_users ( user_id char (36) NOT NULL,
friend_id char (36) NOT NULL);

2- Establish relationships in the user model.

 var $hasAndBelongsToMany = array( 'friend' => array('className' => 'User', 'joinTable' => 'users_users', 'foreignKey' => 'user_id', 'associationForeignKey' => 'friend_id', 'unique' => true, ), ); var $hasMany = array( 'Post' => array( 'className' => 'Post', 'foreignKey' => 'user_id' ), ); 

3 - use scaffolding to insert multiple posts, link to friends, and add posts. 4- Add a function to write a record to the Users controller:

 function get_user($id) { $posts = $this->User->find('first', array( 'conditions' => array('User.id' => $id), 'recursive' => '2' )); pr($posts); } 

5 Now you can query the User table using recursive to pull records using the following command:

http: // test / users / get_user / USER_ID

6- On your output all records will be displayed (recursively), including friends and their messages in the returned data tree, when you pr ($ posts)

I know this is a long post, but I think it will provide the best solution for what you are trying to do. The power of CakePHP is incredible. This is the learning curve that kills us.

Happy coding!

Answer 2

If Post.user_id points to Friend.id (which will not follow convention btw), then it will

 $posts = $this->Post->find('all',array( 'conditions' => array( 'Post.user_id' => $user_id_array ) ); 

which will result in .. WHERE Post.user_id IN (1, 2, 3) ..

Answer 3

Depending on your installation, it might be faster to run two queries rather than trying to link them together through Cake stuff. I would recommend adding something like getFriendsPosts () in the Users model.

 <?php class UserModel extends AppModel { // ... stuff function getFriendsPosts( $user_id ) { $friends = $this->find( ... parameters to get user IDs of all friends ); // flatten the array or tweak your params so they fit the conditions parameter. Check out the Set class in CakePHP $posts = $this->find( 'all', array( 'conditions' => array( 'User.id' => $friends ) ) ); return $posts; } } ?> 

Then, to call it, in the controller just

 $friends = $this->User->getFriendsPosts( $this->Auth->User('id') ); 

NTN, Travis

Answer 4

CakePHP does not already generate efficient code:

 SELECT * from Posts WHERE user_id IN (id1, id2 ...) 

If not, you can do

 $conditions='NULL'; foreach($user_id_array as $id) $conditions.=", $id"; $posts = $this->Posts->find('all', array( 'conditions' => "Post.user_id IN ($conditions)", )); 

Answer 5

If your models are correctly linked, Cake will automatically load the related model entries. Thus, when searching for a specific user, Cake will automatically download related friends and related messages from these friends. All you have to do is set the recursion level high enough.

 $user = $this->User->find('first', array('conditions' => array('User.id' => $id), 'recursive' => 2)); debug($user); // gives something like: array( User => array() Friend => array( 0 => array( ... Post => array() ), 1 => array( ... Post => array() ) ) ) 

All you have to do is extract messages from the friends of the user, which is as simple as:

 $postsOfFriends = Set::extract('/Friend/Post/.', $user);