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?

57
demandé sur Community 2013-08-06 15:46:07

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();
122
répondu driechel 2018-02-14 19:22:47

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();
8
répondu ph4r05 2017-06-24 17:54:17
"151910920 de la Requête", avec des sous-requête dans 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();
0
répondu Amit Meena 2017-08-22 06:52:56

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());
0
répondu cby016 2018-06-15 18:11:41