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

  laravel, laravel-5, mysql, php, sql

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();
    }

Source: Laravel

Leave a Reply