Complex Find in Cake PHP

Question

Hey all I'm hoping someone has enough experience with Cake PHP to make this work.

I'm working on something that at the moment could affectionately be called a twitter clone. Essentially I have a set up like this.

Users have many friends. This is a many to many relationship to the user table. It is stored in a link tabled called friends_users with columns user_id, friend_id. Users is a table with column user_id.

Then i have a table called tips which associates to a user. A user can have many tips.

I want to figure out a way to do a find on the Tip model that returns all tips owned by the userid i pass in as well as any tips owned by any friends of that user.

This SQL query works perfectly -

SELECT *
FROM `tips`
JOIN users ON users.id = tips.user_id
JOIN friends_users ON tips.user_id = friends_users.friend_id
WHERE (friends_users.user_id =2 or tips.user_id=2)
LIMIT 0 , 30

That returns user#2s Tips as well as the tips of anyone who is a friend of User 2.

Now how can i do the same thing using $this->Tip->findxxxxx(user_id)

I know i can use Tip->query if need be but i'm trying to learn the hard way.

Answer 1

In CakePHP speak, many to many is "Has And Belongs To Many" (HABTM). Assuming you've set up the relations properly, what you then need to do is have a two level recursive find, such that the friend you find on retrieves all of their friends, and those friends get their tips loaded. You may have to dynamically bind/unbind models in order to prevent those friends from getting their friends (although that in practice may not be too much of a problem).

The manual entry on associations is a must read.

Answer 2

If all you need in the results of the query is a list of tips, I'd be tempted to do this in 2 queries. The first to find a list of user ids of this user and their friends, the second to find the tips that belong to any one of these ids. So, in your Tip model:

function findTipsByUserAndFriends($userId) {
  //FriendsUser is automagically created by CakePHP "with" association
  $conditions = array('FriendsUser.user_id'=>$userId);
  $fields = 'FriendsUser.friend_id';
  //get a list friend ids for the given user
  $friendIds = $this->Tip->User->FriendsUser->find('list', compact('conditions', 'fields'));
  //get list of all userIds for whom you want the tips
  $userIds = array($userId) + $friendIds;
  $conditions = array('Tip.user_id'=>$userIds);
  $tips = $this->Tip->find('all', compact('conditions'));
  return $tips;
}

Note that you're calling the first find on the automagically created "FriendsUser" model that CakePHP uses to model your HABTM friends_users table, so you don't need to create it.

This is untested, so you might need to debug some of it, but you get the idea.