How can I find all records for a model without doing a long list of "OR" conditions?

Question

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

My associations go like this:

User ->(has many)-> Friends ,
User ->(has many)-> Posts

I'm trying to display a list of all a user's friends recent posts, in other words, list every post that was created by a friend of the current user logged in.

The only way I can think of doing this is by putting all the user's friends' user_ids in a big array, and then looping through each one, so that the find() call would look something like:

$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 this isn't the best way of doing things as if that user is popular that's a lot of OR conditions. Can anyone suggest a better alternative?

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

"Users" table
id
username
etc

"Friends" table
id
user_id
friend_id
etc

"Posts" table
id
user_id
etc

Answer 1

After reviewing what you have rewritten, I think I understand what you are doing. Your current structure will not work. There is no reference in POSTS to friends. So based on the schema you have posted, friends CANNOT add any POSTS. I think what you are trying to do is reference a friend as one of the other users. Meaning, A users FRIEND is actually just another USER in the USERS table. This is a self referential HABTM relationship. So here is what I would propose:

1- First, make sure you have the HABTM table created in the DB:

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

2- Establish the 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 the scaffolding to insert a few records, linking friends and adding posts. 4- Add the view record function 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 the records using the following command:

http://test/users/get_user/USER_ID

6- Your output will show all of the records (recursively) including the friends and their posts 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. It's the learning curve that kills us.

Happy Coding!

Answer 2

If Post.user_id points to Friend.id (which wouldn't follow the convention btw) then it would be

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

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

Answer 3

Depending on your setup, it might be quicker to run two queries rather than trying to chain them together via the Cake stuff. I'd 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 do

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

HTH, Travis

Answer 4

Isn't CakePHP already generating the efficient code of:

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 properly associated, Cake will automatically retrieve related model records. So, when you search for a specific user, Cake will automatically retrieve related friends, and related posts of these friends. All you need 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 need to do is extract the posts from the user's friends, which is as easy as:

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