codeigniter复合连接只有当行不存在 [英] codeigniter complex join only if rows don't exist

问题描述

我在做一个约会应用，很像tindler。用户可以喜欢或不喜欢其他用户，如果两个用户都喜欢彼此，他们可以选择与对方聊天。目前正在建立查询以随机抽取随机配置文件和其中一张照片：

I'm making a dating application, much like tindler. Users can like or dislike other users, and if two users both like each other they get the option to chat with eachother. Currently building the query to pull a random profile and one of their pictures at random:

$data = $this->db
->select('users.id,display_name,city,state,gender,users_pictures.picture')
  ->join('users_pictures','users_pictures.user_id = users.id')
  ->order_by('id','RANDOM')
  ->limit(1)
  ->where(array('users.approved'=>1,'users_pictures.approved'=>1))
  ->where(array('users.id !='=>$user_id))->get('users')->result_array();    

现在这里是我困惑的..我有一个表用于likes_dislikes包括user_id，foreign_user_id ，和event_type（喜欢，不喜欢）。

Now here's where I'm confused.. I have a table for likes_dislikes that consists of user_id, foreign_user_id, and event_type (like, dislike).

如果你不喜欢或喜欢某个用户，我不想在我的结果中。我可以想到处理这个的唯一方法是执行第二个查询，检查这，然后做另一个随机查询，如果你已经喜欢/不喜欢他们，使你只看到你尚未评分的用户。是否有更好的方法？

If you disliked or liked a user already, I do not want them in my results. The only means I can think of handling this is to perform a second query which checks this, and then does another 'random' query if you have already liked/disliked them so that you only see users you haven't already rated. Is there a better way?

任何帮助非常感谢：）

推荐答案

您是否听说过 EXISTS 和 NOT EXISTS MySQL 命令？

Have you heard of the EXISTS and NOT EXISTS MySQL commands? The latter is the one you should try.

SELECT
 u.id, display_name, city, state, gender, up.picture
FROM
 users AS u
INNER JOIN
 users_pictures AS up ON up.user_id = u.id
WHERE
 NOT EXISTS(
    SELECT 
     1 
    FROM
     likes_dislikes
    WHERE
     ld.foreign_user_id = u.id AND ld.user_id = $loggedInUserId
 )

上面将列出所有用户，不包括所有喜欢/不喜欢的用户。尝试形成这个 SQL 语句与 Codeigniter 没有值绑定是有点hacky。

The above will list all users, excluding all liked/disliked users. Trying to form this SQL statement with Codeigniter without the value being binded is a little hacky.

通过Google进行的简短调查（现在可能已过时）表明以下内容应该适用。

A brief investigation via Google (which may now be outdated) suggests that the following should be suitable.

//...rest of query
$db->where("
  NOT EXISTS (
    SELECT 1 
    FROM likes_dislikes 
    WHERE ld.foreign_user_id = u.id AND ld.user_id = $loggedInUserId
  ) AND 1 = ", 1); 

注意最后一部分 AND 1 =，1） / code>。

Take notice to the last part AND 1 = ", 1);.

这篇关于codeigniter复合连接只有当行不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！