CODEWITHSUNDEEP
sqllaravel-4eloquent
Arnold Ewin
Arnold Ewin

Reputation: 1173

leftJoin does not get all the rows in the LEFT table

I have the following query

#Query 1
$count = User::where('users.status', '=', 1)
    ->leftJoin('deposits', 'users.id', '=', 'deposits.user_id')
    ->select('users.*')
    ->groupBy('deposits.user_id')
    ->count();

# Count == 71


# Query 2
User::where('users.status', '=', 1)->count()

# Count == 89

Please why I'm I not getting all the rows in the users table?

Ultmately, I'll like to run the following query

$resellers = User::where('users.status', '=', UserStatus::getUserStatusAsInteger('reseller') )
                    ->leftJoin('deposits', 'users.id', '=', 'deposits.user_id')
                    ->where('deposits.status', '=', 'completed')
                    ->where('deposits.created_at', '>=',  \Carbon\Carbon::now()->subDays(25) )
                    ->having( DB::raw( 'sum( deposits.amount )') , '<', Settings::first()->min_reseller_deposit )
                    ->select('users.*',  DB::raw( 'sum( deposits.amount ) as `total_deposits`' ))
                    ->groupBy('users.id')
                    ->get();

Thanks

Upvotes: 1

Views: 1276

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

In your first query, the reason you don't get all rows back is because you're grouping by deposits.user_id which will not exist for all users.

->groupBy('deposits.user_id')

Instead you should group by users.id

In your second query, the reason you're not getting all rows in the left table is because you're referring to the right table, deposits, in your where clause, which means anytime there is no match on deposits those values will be null and your where fails.

The solution is to move the where conditions for deposits into your left join

leftJoin('deposits', function($join) {
    $join->on('deposits.user_id', '=', 'users.id')
         ->on('deposits.status','=',DB::raw("'completed'"))
         ->on('deposits.created_at', '>=',  \Carbon\Carbon::now()->subDays(25);
})

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Even though you are left joining on a table, you still use a join condition and the query would return rows only that satisfy the condition. As there are no rows in the right table that don't match the condition, you wouldn't see null values showing up.

Upvotes: 1

Related Questions