Laravel Fluent Query Builder Join avec sous-requête

Question

Après des heures de recherche et l'utilisation de DB::select, je dois poser cette question. Parce que je suis sur le point de jeter mon ordinateur ;).

Je veux obtenir la dernière entrée d'un utilisateur (sur la base de l'horodatage). 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 demande d'un autre poste ici sur stackoverflow.

J'ai tout essayé pour y parvenir avec le constructeur de requêtes fluides de Laravel, mais 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 ne m'aide pas beaucoup car je ne vois pas comment je pourrais utiliser une sous-requête à cet endroit ? Quelqu'un peut-il éclairer ma journée ?

Answer 1

Ok pour tous ceux d'entre vous qui sont arrivés ici en cherchant désespérément le même problème. J'espère que vous trouverez cette solution plus rapidement que moi ;O.

C'est ainsi que le problème est résolu. JoostK m'a dit sur github que "le premier argument pour joindre est la table (ou les données) que vous joignez". Et il avait raison.

Voici le code. Les tables et les noms sont différents, mais vous comprenez l'idée, n'est-ce pas ? Il 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 2

Je cherchais une solution à un problème assez proche : trouver les enregistrements les plus récents par groupe, ce qui est une spécialisation d'un problème typique. le plus grand-n par-groupe avec N = 1.

La solution implique le problème que vous traitez ici (c'est-à-dire, comment construire la requête dans Eloquent) donc je la poste car elle pourrait être utile à d'autres. Elle démontre une manière plus propre de construire des sous-requêtes en utilisant l'interface fluide puissante d'Eloquent avec des colonnes de jointure multiples et des colonnes d'entrée. where condition dans la sous-sélection jointe.

Dans mon exemple, je veux récupérer les résultats les plus récents du scan DNS (table scan_dns ) par groupe identifié par watch_id . Je construis la sous-requête séparément.

Le SQL que je veux qu'Eloquent génère :

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

UPDATE :

Desde Laravel 5.6.17 le site jointures de sous-requêtes ont été ajoutés afin qu'il y ait une façon native de construire la requête.

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

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

Answer 3

Je pense que ce que vous recherchez est "joinSub". Il est supporté à partir de la version 5.6 de Laravel. Si vous utilisez une version de Laravel inférieure à 5.6, vous pouvez également l'enregistrer en tant que macro dans votre fichier de fournisseur de service d'application. comme ceci https://github.com/teamtnt/laravel-scout-tntsearch-driver/issues/171#issuecomment-413062522

$subquery = DB::table('catch-text')
            ->select(DB::raw("user_id,MAX(created_at) as MaxDate"))
            ->groupBy('user_id');

$query = User::joinSub($subquery,'MaxDates',function($join){
          $join->on('users.id','=','MaxDates.user_id');
})->select(['users.*','MaxDates.*']);

Answer 4

Requête avec 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();

Answer 5

Je suis sur Laravel 7.25 et je ne sais pas s'il supporte les versions précédentes ou non mais c'est plutôt bien.

Syntaxe de la fonction :

public function joinSub($query, $as, $first, $operator = null, $second = null, $type = 'inner', $where = false)

Exemple :

Afficher/obtenir l'ID de l'utilisateur et le nombre total de messages qu'il a postés en joignant deux tables users et posts.

        return DB::table('users')
            ->joinSub('select user_id,count(id) noOfPosts from posts group by user_id', 'totalPosts', 'users.id', '=', 'totalPosts.user_id', 'left')
            ->select('users.name', 'totalPosts.noOfPosts')
            ->get();

Alternatif :

Si vous ne voulez pas mentionner 'left' pour leftjoin, vous pouvez utiliser une autre fonction préconstruite

    public function leftJoinSub($query, $as, $first, $operator = null, $second = null)
    {
        return $this->joinSub($query, $as, $first, $operator, $second, 'left');
    }

Et oui, il appelle en fait la même fonction mais il passe le type de jointure lui-même. Vous pouvez appliquer la même logique pour d'autres jointures, par exemple righJoinSub(...), etc.