Unable to return counts of 0 (zero) with query builder

Hi there! Hopefully this is an easy one for somebody more eloquent-savvy than myself. With the following query, I am returning info about a user and their team, as well as a count of how many times each particular "stat_meta" type appears in the "basketball_game_events" table. However, when that count returns 0, it just doesn't return anything for that row. I want it to return each "stat_meta" type next to each user/team combo, even if there are 0 "basketball_game_events" to count.

$collection = DB::table('stat_metas')
                    ->select('users.id', 'users.name', 'teams.team_name', 'stat_metas.stat_abr', DB::raw('count(*) as total'))
                    ->leftJoin('basketball_game_events', 'stat_metas.id', '=', 'basketball_game_events.event_type_id')
                    ->leftJoin('users', 'basketball_game_events.player_id', '=', 'users.id')
                    ->leftJoin('user_team_rel', 'users.id', '=', 'user_team_rel.user_id')
                    ->leftJoin('teams', 'user_team_rel.team_id', '=', 'teams.id')
                    ->where('stat_metas.type', '=', 'recorded')
                    ->whereIn('users.id', $players)
                    ->groupBy('users.id', 'users.name', 'teams.team_name', 'stat_metas.stat_abr')
                    ->orderBy('users.name', 'asc')
                    ->orderBy('stat_metas.stat_abr')
                    ->get();

6 years ago

Level 18

You also can use the ->count() method on your connection and catch the 0 response by doing something like this:

if($collection->count()) {
	return "your data";
} else {
	return "nothing";
}

hunterhawley

6 years ago

Level 2

I considered using ->count() but then I cannot return the other things I need like the user and stat_meta type

yjuyjuy

6 years ago

Level 1

Maybe something like this could help?

$query = DB::table('stat_metas')->select(...)->leftJoin(...)->where(...);

$count = $query->count();
$collection = $query->get();

jlrdw

6 years ago

Level 75

if($yourvariable->isEmpty()){

/// code

//////or ////////

if(! empty $yourvariable){
code .................

Please or to participate in this conversation.