[FIXED] How to simplify a SQL "IN" loop query from 1 to multiple IN checks in the same query?

Issue

I'm trying to simplify this part of code where I call a loop DB query to a single DB query that i run a loop over the results afterwards.

My question now is how can I not only get the apps count of one category where a row in the app_categories table exists but the result of every categories count at once?

Current code:

    foreach(DB::Table('categories')->get() as $category)
    {
        $appcount[$category->name] = DB::Table('apps')->where('active', 1)
            ->whereRaw('apps.id in (select app_id from app_categories where category_id = ?)', [$category->id])
            ->count();
    }

Solution

This will suit your needs:

DB::table('categories')
    ->select('categories.id as category_id','categories.name as name',DB::raw('count(*) as apps_count'))
    ->leftJoin('app_categories','categories.id','=','apps_categories.category_id')
    ->leftJoin('apps','apps.id','=','apps_categories.app_id')
    ->where('apps.active',1)
    ->groupBy('categories.id')

Another option could be using eloquent relation.

Answered By - nay

Answer Checked By - Clifford M. (PHPFixing Volunteer)