如何在不使用`$ this-> Model-> query（）`的情况下在CakePHP中编写复杂的查找查询？

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

嘿所有我希望有人有足够的经验与Cake PHP来完成这项工作。

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.

用户有很多朋友。这是与用户表的多对多关系。它存储在名为friends_users的链接表中,其列为user_id,friend_id。用户是一个包含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.

我想找出一种方法来对Tip模型进行查找,该模型返回我传入的用户标识所拥有的所有提示以及该用户的任何朋友拥有的任何提示。

This SQL query works perfectly -

这个SQL查询非常有效 -

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.

这将返回用户#2s提示以及作为User 2的朋友的任何人的提示。

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

现在我怎样才能使用$ this-> Tip-> findxxxxx(user_id)做同样的事情

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

我知道如果需要我可以使用提示 - >查询,但我正在努力学习。

3 个解决方案

#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:

如果您在查询结果中所需要的只是一个提示列表,我很想在2个查询中执行此操作。第一个找到此用户及其朋友的用户ID列表,第二个找到属于这些ID中的任何一个的提示。所以,在你的Tip模型中:

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.

请注意,您正在调用自动​​创建的“FriendsUser”模型上的第一个查找,CakePHP使用该模型来建模您的HABTM friends_users表,因此您无需创建它。

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

这是未经测试的,因此您可能需要调试其中一些,但您明白了。

#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.

希望这可以帮助。

#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).

在CakePHP中,很多人都说“有并且属于许多人”(HABTM)。假设您已正确设置关系,那么您需要做的是进行两级递归查找,以便您找到的朋友检索他们的所有朋友,并且这些朋友会加载他们的提示。您可能必须动态绑定/取消绑定模型以防止这些朋友获取他们的朋友(尽管实际上这可能不是太大的问题)。

The manual entry on associations is a must read.

必须阅读手册上的关联条目。