时间戳

首页 > 解决方案 > Laravel 雄辩的查询具有 JOIN 和子查询

mysql - Laravel 雄辩的查询具有 JOIN 和子查询

问题描述

我必须在 Laravel eloquent （查询生成器）中使用查询，但我不确定如何转换它：

SELECT c.*, m.*,th.team as team_home, ta.team as team_away from schedule as c

LEFT JOIN matches as m ON m.id_match = c.match_id
LEFT JOIN teams as  th ON m.team_h = th.id_team
LEFT JOIN teams as  ta ON m.team_a = ta.id_team

WHERE c.serie_id = :sid and c.days =(

   SELECT c.days from schedule as c
   LEFT JOIN matches as m ON m.id_match = c.match_id
   WHERE c.serie_id = :sid and m.match_stato = 2 order by c.days DESC LIMIT 1

) order by c.days, th.team ASC

正如您所注意到的，它有 3 个 JOINS 和一个子查询，而不是两个订单。如何在 Eloquent 中使用它？

标签： mysqllaraveleloquent

解决方案

对于这种性质的子查询，您可以使用whereIn().

此外，对于子查询内部的逻辑order by c.days DESC LIMIT 1，您可以只max(c.days)在 select 子句中使用，而不是 。

DB::table('schedule as c')
    ->join('matches as m', 'm.id_match', '=', 'c.match_id')
    ->join('teams as th', 'm.team_h', '=', 'th.id_team')
    ->join('teams as ta', 'm.team_a', '=', 'ta.id_team')

    ->where('c.serie_id', '=', $sid)
    ->whereIn('c.days', function($query) use($sid){
        $query->select('max(c.days)')
            ->from('schedule as c2')
            ->join('matches as m2', 'm2.id_match', '=', 'c2.match_id')
            ->where('c2.serie_id', '=', $sid)
            ->where('m2.match_stato', '=', 2);
    }) 
    ->select('c.*', 'm.*', 'th.team as team_home', 'ta.team as team_away')

    ->orderBy('c.days', 'asc')
    ->orderBy('th.team', 'asc')
    ->get();      

######

推荐阅读