Laravel constructeur de requête Fluent joindre avec subquery
OK après des heures de recherche et toujours en utilisant DB::sélectionnez je dois poser cette question. Parce que je suis sur le point de retirer mon ordinateur ;).
je veux obtenir la dernière entrée d'un utilisateur (de base sur l'horodateur). Je peux le faire avec le sql brut
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
j'ai reçu cette requête d'un autre post ici sur stackoverflow.
j'ai tout essayé pour le faire avec le constructeur de requête fluent dans Laravel cependant sans succès.
je sais que le manuel dit que vous pouvez le faire:
DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
mais cela n'aide pas beaucoup parce que je ne vois pas comment je pourrais utiliser un subquery là-bas? N'importe qui qui peut éclairer ma journée?
4 réponses
Ok pour vous tous qui êtes arrivés ici en désespoir de cause, cherchant le même problème. J'espère que vous trouverez ce plus rapide que j'ai fait ;O.
c'est comme ça qu'on le résout. JoostK m'a dit à github que "le premier argument à rejoindre est la table (ou les données) que vous rejoignez.". Et il avait raison.
voici le code. Différentes tables et les noms mais vous aurez une bonne idée? It 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();
je cherchais une solution à tout un problème connexe: trouver les enregistrements les plus récents par groupe qui est une spécialisation d'un typique le plus-n-Par-Groupe avec N = 1.
la solution implique le problème que vous traitez ici (c.-à-d., Comment construire la question dans Eloquent) donc je l'affiche car il pourrait être utile pour d'autres. Il démontre une façon plus propre de la construction de sous-requête en utilisant une interface éloquente puissante fluent avec plusieurs colonnes de jointure et la condition where
à l'intérieur de la sous-sélection jointed.
dans mon exemple, je veux récupérer les plus récents résultats de balayage DNS (tableau scan_dns
) par groupe identifié par watch_id
. Je construis la sous-requête séparément.
Le SQL je veux Éloquent à générer:
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`
Je l'ai fait de la manière suivante:
// 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();
$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();
je ne peux pas commenter parce que ma réputation n'est pas assez élevé. @Franklin Rivero si vous utilisez Laravel 5.2 vous pouvez définir les fixations sur la requête principale au lieu de la jointure en utilisant la méthode setBindings.
donc la requête principale dans la réponse de @ph4r05 ressemblerait à quelque chose comme ceci:
$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());