Sql select row if there is no row in another table

I have a table users

and a table likes

. The user receives the displayed random other user data and can decide whether they like it or not. I am struggling with picking a new random partner for a user he hasn't rated yet!

Now I am trying to select all users who do not have a row in the table likes

, with an estimate of the relationship of the current user user

to the rated user partner

.

Table users

is a standard user table in likes

I have 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' );

      

This is wrong because I am not getting any new user selected with this attempt. I think this is because likes

no rows were found! There are no lines if it hasn't evaluated yet, so there is no result. Is it 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: select

db_users .* from

db_users where

db_users .

id <> IO8fMLYUPHfX1HrwkAWc2xqX and

db_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 DECISION:

**

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

      

+3


source to share


3 answers


You can use not exists

to select all users that have not yet been associated with a specific user.

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

+2


source


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 join like table to get all users, even if they didn't like it.



In Laravel, this can be as simple as changing the connection to be left:

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

      

+1


source


I don't know about Laravel Eloquent, but in SQL you can filter them using 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'

      

+1


source







All Articles