Sql select row if no row exists in other table -

Sql select row if no row exists in other table -

i have users table , likes table. user gets displayed random other user's info , can decide whether likes or dislikes it. struggling selecting new random user partner acting user has not yet rated!

now, trying select users not have row in likes table, relation rating of acting user user rated user partner.

the users table standard user table, in likes have columns, id, user, partner , relation.

i using laravel eloquent, can utilize raw sql.

my attempt:

// $ouser->id 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 wrong, because not new user selected attempt. think because there can not found row in likes ! there no rows when has not yet rated, there no result. correct?

edit:

$osearch = db_user:: select( 'db_users.*' ) ->where( 'db_users.id', '<>', $ouser->id ) ->where( 'db_users.sex', '=', $strsex ) ->whereraw( "not exists ( select 1 db_likes db_likes.relation in ('dislike','like','maybe') , db_likes.user = " .$ouser->id . " , 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 , not exists ( select 1 db_likes db_likes.relation in ('dislike','like','maybe') , db_likes.user = io8fmlyuphfx1hrwkawc2xqx , db_likes.partner = db_users.id ) order 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 db_likes db_likes.relation in ('dislike','like','maybe') , db_likes.user = '" .$ouser->id . "' , db_likes.partner = db_users.id )" );

you can utilize not exists select users have not partnered user yet

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

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

sql