Laravel Fluent Query Builder 하위 쿼리와 조인

Laravel Fluent Query Builder 하위 쿼리와 조인

---

몇 시간 동안 연구 한 후에도 여전히 DB :: select I have to ask this question. 나는 내 컴퓨터를 샅샅이 뒤 지려고하기 때문에;).

사용자의 마지막 입력 (타임 스탬프 기준)을 얻고 싶습니다. 원시 SQL로이 작업을 수행 할 수 있습니다.

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

나는 다른 포스트에서이 쿼리를 가지고 여기 에 유래에 있습니다.

나는 Laravel의 유창한 쿼리 빌더를 사용하여 모든 것을 시도했지만 성공하지 못했습니다.

나는 매뉴얼에 당신이 이것을 할 수 있다고 말합니다.

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

그러나 하위 쿼리를 어떻게 사용할 수 있는지 알지 못하기 때문에별로 도움이되지 않습니다. 내 하루를 밝힐 수있는 사람?

---

같은 문제를 찾아 필사적으로 여기에 도착한 여러분 모두에게 좋습니다. 내가 한 것보다 더 빨리 찾을 수 있기를 바랍니다.

이것이 해결 방법입니다. JoostK는 github에서 "조인 할 첫 번째 인수는 조인하는 테이블 (또는 데이터)"이라고 말했습니다. 그리고 그는 옳았습니다.

다음은 코드입니다. 테이블과 이름이 다르지만 올바른 아이디어를 얻을 수 있습니까? 그것은 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();

---

나는 상당히 관련된 문제에 대한 해결책을 찾고 있었다 : N = 1 인 전형적인 그룹당 최고 n 개의 전문화 인 그룹당 최신 레코드 찾기 .

해결책은 여기서 다루고있는 문제 (즉, Eloquent에서 쿼리를 작성하는 방법)를 포함하므로 다른 사람들에게 도움이 될 수 있으므로 게시하고 있습니다. where결합 된 하위 선택 내부에 여러 개의 결합 열과 조건이있는 강력한 Eloquent 유창한 인터페이스를 사용하여 하위 쿼리 생성의 더 깨끗한 방법을 보여줍니다 .

내 예에서는로 scan_dns식별되는 그룹당 최신 DNS 스캔 결과 (표 ) 를 가져오고 싶습니다 watch_id. 하위 쿼리를 별도로 작성합니다.

Eloquent가 생성하기를 원하는 SQL :

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`

다음과 같은 방식으로 수행했습니다.

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

최신 정보:

이후 Laravel 5.6.17 하위 쿼리는 조인 쿼리를 구축 할 수있는 기본 방법이 있도록 추가되었습니다.

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

---

당신이 찾고있는 것은 "joinSub"라고 생각합니다. laravel ^ 5.6에서 지원됩니다. 5.6 이하의 laravel 버전을 사용하는 경우 앱 서비스 공급자 파일에 매크로로 등록 할 수도 있습니다. 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.*']);

---

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

---

I can't comment because my reputation is not high enough. @Franklin Rivero if you are using Laravel 5.2 you can set the bindings on the main query instead of the join using the setBindings method.

So the main query in @ph4r05's answer would look something like this:

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

---

You can use following addon to handle all subquery related function from laravel 5.5+

https://github.com/maksimru/eloquent-subquery-magic

User::selectRaw('user_id,comments_by_user.total_count')->leftJoinSubquery(
  //subquery
  Comment::selectRaw('user_id,count(*) total_count')
      ->groupBy('user_id'),
  //alias
  'comments_by_user', 
  //closure for "on" statement
  function ($join) {
      $join->on('users.id', '=', 'comments_by_user.user_id');
  }
)->get();

참고URL : https://stackoverflow.com/questions/18079281/laravel-fluent-query-builder-join-with-subquery