Mysql 非基础常用查询方式

where in 查询结果排序

默认情况下返回结果不会按照in语句内的数据顺序排序。如果需要返回结果按照in语句内的数据顺序排序，可以通过如下方式实现。

第一种 order by FIND_IN_SET(str,strlist)

select * from test where id in (2,1,4,3,5) order by find_in_set id (id,'2,1,4,3,5')

第二种 order by SUBSTRING_INDEX(str,delim,count)

select * from test where id in (2,1,4,3,5) order by substring_index('2,1,4,3,5',id,1)

第三种 order by field

select * from test where id in (2,1,4,3,5) order by field(id,2,1,4,3,5)

一对多 关联 对查询结果 用“多”表的字段进行排序

关系是一 的表 requirement_order

关系为多的表 goods_sku 关联字段为 requirement_id

对requirement_order表进行查询并按照 goods_sku 表中 price 价格进行倒排序

select * from requirement_order ORDER BY (select goods_sku.price from goods_sku where goods_sku.requirement_id = requirement_order.id limit 1) desc

一对多限制从表条数查询，比如查出所有分类以及每个分类下n条文章

表结构如下

方法一

laravel构造器写法

$category = Category::select('id', 'name')
    ->with([
     'article'=>function ($query) {
       $query->select('id', 'cat_id', 'title', 'author')
             ->whereRaw('(select count(*) from articles as articles2 where articles2.cat_id = articles.cat_id and articles2.id >= articles.id) <= ?', [3]);
                }
    ])
    ->get();

代码执行SQL

//查询分类
select `id`,`name` from `categories`
//查询分类下文章，每个分类下3条
select
  `id`,
  `cat_id`,
  `title`,
  `author`
from
  `articles`
where
  `articles`.`cat_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
  and (
    select
      count(*)
    from
      articles as articles2
    where
      articles2.cat_id = articles.cat_id
      and articles2.id >= articles.id
  ) <= 3

如果只是查询一条的情况可以在with中使用groupBy从句

多对多限制从表条数查询，查出所有书单以及每个书单下n本书籍

表结构如下

方法一

适用于数据量少的情况,数据多的时候查询慢

//书单模型BookList定义限制条数关联recommendBook
public function recommendBook()
{
  return $this->belongsToMany('App\Models\Book', 'book_list_has_books', 'booklist_id', 'book_id')
     ->select('books.id', 'name', 'cover', 'author')
     ->orderBy('book_list_has_books.sort', 'asc')
     ->latest('book_list_has_books.created_at')
     ->limit(10);
}

//控制器中查询
$booklists = BookList::select('id', 'title', 'position')
   ->latest()
   ->get()
   ->each(function ($booklist) {
        $booklist->load('recommendBook');
   });

代码执行SQL

循环查询

方法二

适用于从表数据量少的情况，相比第一种方法减少了查询次数，但是增加了从表数据量（因为是查所有），会占更多的用内存,可能导致内存泄露

$booklists = BookList::select('id', 'title', 'position')
  ->with([
     'listHasBook'=>function ($query) {
         $query->select('books.id', 'name', 'cover', 'author')
               ->orderBy('book_list_has_books.sort', 'asc')
               ->latest('book_list_has_books.created_at');
      }
   ])
  ->when($request->input('position'), function ($query) use ($request) {
      $query->where('position', $request->input('position'));
   })
  ->orderBy('sort', 'asc')
  ->latest()
  ->get()
  ->each(function (&$booklist) {
     $booklist->books = $booklist->listHasBook->slice(0, 10)->all();
     unset($booklist->listHasBook);
  });

执行SQL

只查询两次

如果只是查询一条的情况可以在with中使用groupBy从句

$booklist = BookList::select('id', 'title')
  ->with([
      'listHasBook'=>function ($query) {
           $query->select('books.id', 'cover')
                 ->groupBy('booklist_id');
       }
  ])

参考

https://blog.csdn.net/zdw19861127/article/details/80449691

https://bbs.csdn.net/topics/390969673?page=1

laravel with 查询列表限制条数