Sql select row if no row exists in other table

Question

I have a users table and a likes table. A user gets displayed a random other user's data and can decide whether he likes or dislikes it. I am struggling with selecting a new random user partner for the acting user that he has not yet rated!

Now, I am trying to select all users that do not have a row in the likes table, with a relation rating of the acting user user to the rated user partner.

The users table is a standard user table, in likes I have the columns, id, user, partner and relation.

I am using Laravel Eloquent, but can also use raw sql.

My attempt:

// $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' );

It is wrong, because I do not get any new user selected with this attempt. I think it is because there can not be found any row in likes ! There are no rows when he has not yet rated, so there is no result. Is this correct?

EDIT:

$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: "{"error":{"type":"Illuminate\\Database\\QueryException","message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'IO8fMLYUPHfX1HrwkAWc2xqX' in 'where clause' (SQL: selectdb_users.* fromdb_userswheredb_users.id<> IO8fMLYUPHfX1HrwkAWc2xqX anddb_users.sex= 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}}"

**

FINAL SOLUTION:

**

$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 )" );

What's wrong with your attempt? Describing the behavior you're seeing and the desired behavior of your existing attempt can be helpful. Sample data is even better. — Hart CO, Oct 08 '14 at 17:38
What do you mean by "with a relation rating of the acting user user to the rated user partner."? Are you trying to simply find a new user that the user hasn't partnered with (what the rest of the question and sample seems) or are you trying to find ones that have likes for the same people the current user likes, but that haven't been liked themselves yet like this statement makes it seem? — Jason Goemaat, Oct 08 '14 at 18:05
The first one! Just a new user that the user has not yet rated whatsoever! — Shlomo, Oct 08 '14 at 18:07

score 2 · Accepted Answer · answered Oct 08 '14 at 18:06

2

You can use not exists to select all users that have not partnered with a certain user yet

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

answered Oct 08 '14 at 18:06

FuzzyTree

32,014
3
54
85

I am trying to put this into my laravel query, but I am getting an sql error, please see my edit – Shlomo Oct 08 '14 at 18:23
@Indianer you need to quote your variable in the `whereRaw` i.e. `db_likes.user = '" .$oUser->id . "'` – FuzzyTree Oct 08 '14 at 18:45

Avitus · Answer 2 · 2014-10-08T18:02:50.710

I think the sql you want is:

    select db_users.*, db_likes.* from db_users 
left join db_likes on db_users.id = db_likes.partner
        and db_likes.user =  'Sx12cltADam2XvzBMUMwq7DG'
        and db_likes.relation <> 'dislike'
        and db_likes.relation <> 'like'
         and db_likes.relation <> 'maybe'
where db_users.id != 'Sx12cltADam2XvzBMUMwq7DG'

What you want to do is left join the likes table to get all the users even if they didn't like anything.

In Laravel it might be just as simple as changing the join to be left join:

   $oSearch = Db_User::
                  select( 'db_users.*', 'db_likes.*' )
                  ->where( 'db_users.id', '<>', $oUser->id )                 
                  ->leftjoin( 'db_likes', function($join)
                  {
                       $join->on('db_users.id', '=', 'db_likes.partner' )
                        ->on('db_likes.user', '=', $oUser->id )
                        ->on('db_likes.relation', '<>', 'dislike' )
                        ->on('db_likes.relation', '<>', 'like' )
                        ->on('db_likes.relation', '<>', 'maybe' )
                  }
                  );

Thanks I am testing it, but theres an PHP error saying in the left join inner function, $oUser is missing. How can I pass it? — Shlomo, Oct 08 '14 at 18:01
Thanks, but it is still returning all users, although there is one row in likes, that should exclude this one user — Shlomo, Oct 08 '14 at 18:06

score 1 · Answer 3 · answered Oct 08 '14 at 18:10

I don't know about Laravel Eloquent, but in SQL you could filter those out with a subquery in the where clause:

-- sample user id SxKJAhu7LRp8zj6nXZ5g0JRh has partner IO8fMLYUPHfX1HrwkAWc2xqX
-- that should not show up
SELECT * from db_users
WHERE id NOT IN (SELECT partner
        FROM db_likes
        WHERE user = 'SxKJAhu7LRp8zj6nXZ5g0JRh')
    AND id <> 'SxKJAhu7LRp8zj6nXZ5g0JRh'

Sql select row if no row exists in other table

FINAL SOLUTION:

3 Answers3

Linked