dongnao6858
2015-10-31 09:18 阅读 168
已采纳

在Laravel中创建子查询 - 查询构建器

I try to create a sub query. For now, my two queries are :

select `key` from messages group by `key`;

and

select * from messages where `key` = 'KEY_RECUP_AU_DESSUS' order by created_at DESC LIMIT 1;

The aim is to highlight my table messages all elements grouped by key and keeping the last element ( created_at desc)

Thank you

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dsuvs66406 dsuvs66406 2015-10-31 19:04

    You can do this with two different ways.

    Query Builder:

    DB::table('messages AS a')
    ->leftJoin('messages AS b', function($join) {
        $join->on('a.created_at', '<', 'b.created_at');
        $join->on('a.key', '=', 'b.key');
    })
    ->groupBy(['a.key', 'a.message', 'a.created_at'])
    ->havingRaw('COUNT(b.key) < 1')
    ->select(['a.key', 'a.message', 'a.created_at'])
    ->get();
    

    Another way with PARTITION BY

    SELECT 
          key,
          message,
          created_at
    FROM (
        SELECT 
              key, 
              message, 
              created_at, 
              rank() OVER (PARTITION BY key ORDER BY created_at DESC) AS rank 
        FROM 
              messages
    ) AS foo WHERE foo.rank = 1;
    

    if we assume you have table like this.

    postgres=# select * from messages;
     id |   key   |        message         |         created_at
    ----+---------+------------------------+----------------------------
      1 | contact | send from contact page | 2015-10-31 19:45:16.850698
      2 | contact | contact page message   | 2015-10-31 19:45:34.417231
      3 | product | product 1              | 2015-10-31 19:45:44.49584
      4 | product | product 2              | 2015-10-31 19:45:46.856691
      5 | contact | hello it is me         | 2015-10-31 18:45:35.801967
      6 | about   | who are you            | 2015-10-31 19:46:04.123369
      7 | product | product 3              | 2015-10-31 19:46:12.414364
      8 | about   | hi guys                | 2015-10-31 19:46:18.23442
    (8 rows)
    

    The result will be like this with two different query

    postgres=# select key, message, created_at from (select key, message, created_at, rank() over (partition by key order by created_at desc) as rank from messages) foo where rank = 1;
       key   |       message        |         created_at         
    ---------+----------------------+----------------------------
     about   | hi guys              | 2015-10-31 19:46:18.23442  
     contact | contact page message | 2015-10-31 19:45:34.417231 
     product | product 3            | 2015-10-31 19:46:12.414364 
    (3 rows)
    
    点赞 评论 复制链接分享
  • dongzhuo8210 dongzhuo8210 2015-10-31 12:30

    Something like this should work:

    $data = DB::table('messages')->whereIn('key',function($q) {
        $q->select('key')->from('messages')->groupBy('key');
    })->latest()->take(1)->get();
    
    点赞 评论 复制链接分享

相关推荐