Selecting items with join and without foreign key

Question

I have query which gets each user's record from blacklist table and relations to them from rules table.

It looks like that:

$result1 = DB::table('blacklist')
        ->join('rules', 'blacklist.rule_id', '=', 'rules.id')
        ->select('blacklist.*', 'rules.clicks', 'rules.minutes')
        ->groupBy('blacklist.address')
        ->where('blacklist.user_id', JWTAuth::user()->id)
        ->get();

However I set field blacklist.rule_id as CAN BE NULL. Above query doesn't get records which have blacklist.rule_id == null(only these with values).

I have second query for that purpose:

$result2 = DB::table('blacklist')
            ->select('blacklist.*')
            ->groupBy('blacklist.address')
            ->where('blacklist.user_id', JWTAuth::user()->id)
            ->where('blacklist.rule_id', null)
            ->get();

But how to do it in one query?

https://stackoverflow.com/questions/24533406/sql-where-id-equals-id-but-can-be-null this seems to tackle your problem. A left outer join should be working for you - together with an adapted where method — Frnak, Sep 06 '18 at 14:01

score 2 · Accepted Answer · answered Sep 06 '18 at 14:02

It is actually an leftJoin instead of join:

$result1 = DB::table('blacklist')
    ->leftJoin('rules', 'blacklist.rule_id', '=', 'rules.id')
    ->select('blacklist.*', 'rules.clicks', 'rules.minutes')
    ->groupBy('blacklist.address')
    ->where('blacklist.user_id', JWTAuth::user()->id)
    ->get();

score 0 · Answer 2 · answered Sep 06 '18 at 14:05

try this

$result2 = DB::table('blacklist')
                ->select('blacklist.*')
                ->groupBy('blacklist.address')
                ->where(function ($result2) {
                $result2->where('blacklist.user_id', JWTAuth::user()->id)
                    ->orWhereNull('blacklist.user_id');
            }
                ->get();

Selecting items with join and without foreign key

2 Answers2