Laravel: how to use derived tables / subqueries in the laravel query builder
Your first try looks pretty close. Try this:
I removed the long namespace reference and suggest you add a use statement to make your code more readable
$productIds = [ 1, 2, 3, 4, 5 ];$subQuery = DB::table('attribute_options AS counted')->selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count') ->whereIn('counted.product_id', $productIds) ->groupBy('counted.attribute_option_id')$query = AttributeOption::selectRaw('IFNULL(counted.product_count, 0) AS product_count, uncounted.value, uncounted.attribute_id, uncounted.attribute_option_id') ->from(\DB::raw(' ( ' . $subQuery->toSql() . ' ) AS counted ')) ->mergeBindings($subQuery->getQuery()) ->rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id') ->groupBy('attribute_option_id') ->get();