如果其他表中不存在行，Sql 选择行

Question

我有一个users表和一个likes table 。用户会随机显示其他用户的数据，并可以决定他是否喜欢它。我正在努力为尚未评分的代理用户选择一个新的随机用户合作伙伴!

现在，我尝试选择 likes 中没有行的所有用户表，其中包含代理用户的关系评级 user致评分用户partner .

users table 是一个标准用户表，位于 likes我有专栏，id , user , partner和relation .

我使用 Laravel Eloquent，但也可以使用原始 sql。

我的尝试:

// $oUser->id is the acting user
$oSearch = Db_User::
              select( 'db_users.*', 'db_likes.*' )
              ->where( 'db_users.id', '<>', $oUser->id )
              ->where( 'db_likes.user', '=', $oUser->id )
              ->where( 'db_likes.relation', '<>', 'dislike' )
              ->where( 'db_likes.relation', '<>', 'like' )
              ->where( 'db_likes.relation', '<>', 'maybe' )
              ->join( 'db_likes', 'db_users.id', '=', 'db_likes.partner' );

这是错误的，因为我没有通过这次尝试选择任何新用户。我认为这是因为 likes 中找不到任何行!当他尚未评分时没有行，因此没有结果。这是正确的吗？

编辑:

$oSearch = Db_User::
              select( 'db_users.*' )
              ->where( 'db_users.id', '<>', $oUser->id )
              ->where( 'db_users.sex', '=', $strSex )
              ->whereRaw( "not exists ( select 1 from db_likes where db_likes.relation in ('dislike','like','maybe') and db_likes.user = " .$oUser->id . " and db_likes.partner = db_users.id )" );

错误:"{"error":{"type":"Illuminate\\Database\\QueryException","message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'IO8fMLYUPHfX1HrwkAWc2xqX' in 'where clause' (SQL: select db_users .* from db_users where db_users . ID <> IO8fMLYUPHfX1HrwkAWc2xqX and db_users .性别= w and not exists ( select 1 from db_likes where db_likes.relation in ('dislike','like','maybe') and db_likes.user = IO8fMLYUPHfX1HrwkAWc2xqX and db_likes.partner = db_users.id ) order by RAND() limit 1)","file":"\/Applications\/MAMP\/htdocs\/adamundeva-server\/adamundeva\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php","line":625}}"

**

最终解决方案:

**

$oSearch = Db_User::
              select( 'db_users.*' )
              ->where( 'db_users.id', '<>', $oUser->id )
              ->where( 'db_users.sex', '=', $strSex )
              ->whereRaw( "not exists ( select 1 from db_likes where db_likes.relation in ('dislike','like','maybe') and db_likes.user = '" .$oUser->id . "' and db_likes.partner = db_users.id )" );

Answer 1

您可以使用不存在来选择尚未与特定用户合作的所有用户

select * from
db_users dbu
where not exists (
    select 1 from db_likes dbl
    where dbl.relation in ('dislike','like','maybe') -- not sure if this is necessary
    and dbl.user = $oUser->id
    and dbl.partner = dbu.id
)

http://sqlfiddle.com/#!2/8c3bb9/6