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 )" );
source to share
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
)
source to share
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' )
}
);
source to share
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'
source to share