Laravel: cómo usar tablas / subconsultas derivadas en el generador de consultas laravel

Editar:

Aunque esta pregunta originalmente era específica para la consulta que describo debajo, la respuesta que obtuve se aplica a casi todas las preguntas relacionadas con el uso de tablas / subconsultas derivadas en Laravel

Pregunta Original:

Últimamente estoy un poco atrapado en el generador de consultas laravel. Tiene algunas características realmente agradables, pero siento que simplemente no se construye para operaciones de bases de datos más complejas.

Esta es la consulta que bash construir:

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' 

Explicación de la consulta: estoy construyendo una búsqueda facetada para mi catálogo de productos en laravel. Los productos se reducen en función de los filtros / atributos que los usuarios proporcionan. Para una mejor experiencia del usuario, quiero mostrar la cantidad de productos que quedan para cada filtro, eso es lo que hace la consulta anterior: contar todos los productos para un determinado atributo DONDE el product_id está dentro de un conjunto de identificadores de productos.

Mi bash:

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

Por favor, ayúdenme porque no me gusta usar una instrucción DB :: raw () o DB :: select (). Eso no se sentiría “Laravelish” o “Eloquent”.

    Tu primer bash parece bastante cercano. Prueba esto:

    Eliminé la referencia larga del espacio de nombres y sugiero que agregue una statement de use para que su código sea más legible.

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