Laravel: как использовать производные таблицы / подзапросы в конструкторе запросов laravel

Редактировать:

Хотя этот вопрос изначально был специфичен для запроса, который я описываю ниже, полученный ответ относится почти ко всем вопросам, связанным с использованием производных таблиц / подзапросов в Laravel.

Оригинальный вопрос:

В последнее время я немного застрял в построителе запросов laravel. У него есть действительно хорошие функции, но я чувствую, что он не предназначен для более сложных операций с базой данных.

Это запрос, который я пытаюсь построить:

select

'IFNULL(counted.product_count, 0) AS product_count',
'uncounted.value',
'uncounted.attribute_id',
'uncounted.attribute_option_id'

from (

select
'counted.id',
'counted.attribute_id',
'counted.value',
'count(counted.attribute_id) AS product_count'

from `attribute_options` as `counted`
where `counted.product_id` in (?, ?, ?, ?, ?)
group by `counted.attribute_option_id`

) as 'counted'

right join 'attribute_options' as 'uncounted'
on 'counted.id' = 'uncounted.id'

group by 'attribute_option_id'

Объяснение запроса:
Я строю многогранный поиск своего каталога товаров в Laravel. Продукты сужаются на основе фильтров / атрибутов, предоставляемых пользователями. Для лучшего пользовательского опыта я хочу показать количество продуктов, оставшихся для каждого фильтра, это то, что делает запрос выше: подсчет всех продуктов для определенного атрибута, ГДЕ product_id находится в массиве идентификаторов продуктов.

Моя попытка:

    $productIds = [ 1, 2, 3, 4, 5 ];

$subQuery = \DB::table('attribute_options')->selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count')
->from('attribute_options AS counted')
->whereIn('counted.product_id', $productIds)
->groupBy('counted.attribute_option_id')
->mergeBindings($subQuery);

$query = Model::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 '))
->rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id')
->groupBy('attribute_option_id')
->get();

Пожалуйста, помогите мне, потому что я не люблю использовать оператор DB :: raw () или DB :: select (). Это не будет восприниматься как «Laravelish» или «Eloquent».