How can I write a complex find query in CakePHP without using `$this->Model->query()`?

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

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.

Answer 2

I would recommend that you use containable it works great.

$aCond = array(
            'fields' => array(
                'Discount.*'
            ),
            'contain' => array(
                'Event' => array(
                    'conditions' => array('Event.id' => $iEventId)
                )
            )
        );
$aBulkDiscounts = $this->Discount->find('first', $aCond);

hope this helps.

Answer 3

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.