wheredate - Laravel Fluent Query Builder Únase con la subconsulta

wheredate laravel

Question

De acuerdo después de horas de investigación y aún usando DB :: select, tengo que hacer esta pregunta. Porque estoy a punto de pasar por mi computadora;).

Quiero obtener la última entrada de un usuario (base en la marca de tiempo). Puedo hacer esto con el sql en bruto

SELECT c.*, p.*
FROM users c INNER JOIN
(
SELECT user_id,
MAX(created_at) MaxDate
FROM `catch-text`
GROUP BY user_id
) MaxDates ON c.id = MaxDates.user_id INNER JOIN
`catch-text` p ON MaxDates.user_id = p.user_id
AND MaxDates.MaxDate = p.created_at

Recibí esta consulta de otra publicación here en stackoverflow.

Sin embargo, he intentado todo para hacer esto con el generador de consultas fluido en Laravel sin éxito.

Sé que el manual dice que puedes hacer esto:

DB::table(''users'')
->join(''contacts'', function($join)
{
$join->on(''users.id'', ''='', ''contacts.user_id'')->orOn(...);
})
->get();

Pero eso no está ayudando mucho porque no veo cómo podría usar una subconsulta allí. ¿Alguien que pueda iluminar mi día?

Answer 1

Consulta con sub consulta en Laravel

$resortData = DB::table(''resort'')
->leftJoin(''country'', ''resort.country'', ''='', ''country.id'')
->leftJoin(''states'', ''resort.state'', ''='', ''states.id'')
->leftJoin(''city'', ''resort.city'', ''='', ''city.id'')
->select(''resort.*'', ''country.name as country_name'', ''states.name as state_name'',''city.name as city_name'', DB::raw("(SELECT GROUP_CONCAT(amenities.name) from resort_amenities LEFT JOIN amenities on amenities.id= resort_amenities.amenities_id WHERE resort_amenities.resort_id=resort.id) as amenities_name"))->groupBy(''resort.id'')
->orderBy(''resort.id'', ''DESC'')
->get();

Answer 2

Está bien para todos ustedes que llegaron aquí desesperados en busca del mismo problema. Espero que encuentres esto más rápido que yo;

Así es como está resuelto. JoostK me dijo en github que "el primer argumento para unirse es la tabla (o datos) a la que se está uniendo". Y él tenía razón.

Aquí está el código. Diferentes tablas y nombres pero obtendrás la idea ¿verdad? Es t

DB::table(''users'')
->select(''first_name'', ''TotalCatches.*'')
->join(DB::raw(''(SELECT user_id, COUNT(user_id) TotalCatch,
DATEDIFF(NOW(), MIN(created_at)) Days,
COUNT(user_id)/DATEDIFF(NOW(), MIN(created_at))
CatchesPerDay FROM `catch-text` GROUP BY user_id)
TotalCatches''),
function($join)
{
$join->on(''users.id'', ''='', ''TotalCatches.user_id'');
})
->orderBy(''TotalCatches.CatchesPerDay'', ''DESC'')
->get();

Answer 3

Estaba buscando una solución a un problema bastante relacionado: encontrar los registros más nuevos por grupo, que es una especialización de un típico greatest-n-per-group típico con N = 1.

La solución implica el problema que está tratando aquí (es decir, cómo crear la consulta en Eloquent), así que lo estoy publicando, ya que podría ser útil para otros. Demuestra una forma más clara de construcción de subconsulta utilizando una poderosa interfaz fluida Eloquent con múltiples columnas de unión y where condición dentro de la sub-selección se unió.

En mi ejemplo, quiero buscar los resultados de escaneo DNS más nuevos (tabla scan_dns ) por grupo identificado por watch_id . Construyo la subconsulta por separado.

El SQL que quiero que Eloquent genere:

SELECT * FROM `scan_dns` AS `s`
INNER JOIN (
SELECT x.watch_id, MAX(x.last_scan_at) as last_scan
FROM `scan_dns` AS `x`
WHERE `x`.`watch_id` IN (1,2,3,4,5,42)
GROUP BY `x`.`watch_id`) AS ss
ON `s`.`watch_id` = `ss`.`watch_id` AND `s`.`last_scan_at` = `ss`.`last_scan`

Lo hice de la siguiente manera:

// table name of the model
$dnsTable = (new DnsResult())->getTable();
// groups to select in sub-query
$ids = collect([1,2,3,4,5,42]);
// sub-select to be joined on
$subq = DnsResult::query()
->select(''x.watch_id'')
->selectRaw(''MAX(x.last_scan_at) as last_scan'')
->from($dnsTable . '' AS x'')
->whereIn(''x.watch_id'', $ids)
->groupBy(''x.watch_id'');
$qqSql = $subq->toSql(); // compiles to SQL
// the main query
$q = DnsResult::query()
->from($dnsTable . '' AS s'')
->join(
DB::raw(''('' . $qqSql. '') AS ss''),
function(JoinClause $join) use ($subq) {
$join->on(''s.watch_id'', ''='', ''ss.watch_id'')
->on(''s.last_scan_at'', ''='', ''ss.last_scan'')
->addBinding($subq->getBindings());
// bindings for sub-query WHERE added
});
$results = $q->get();

Answer 4

No puedo comentar porque mi reputación no es lo suficientemente alta. @Franklin Rivero si está utilizando Laravel 5.2 puede establecer los enlaces en la consulta principal en lugar de la unión mediante el método setBindings.

Entonces, la consulta principal en la respuesta de @ ph4r05 se vería así:

$q = DnsResult::query()
->from($dnsTable . '' AS s'')
->join(
DB::raw(''('' . $qqSql. '') AS ss''),
function(JoinClause $join) {
$join->on(''s.watch_id'', ''='', ''ss.watch_id'')
->on(''s.last_scan_at'', ''='', ''ss.last_scan'');
})
->setBindings($subq->getBindings());