tp5 有点技巧性的sql语句

in查询,默认会将查询的条件按数字正序排序,那我们不需要排序怎么办?

$exp = new \think\db\Expression('field(id,'.implode(',',$province_ids).')');

$list = Db::name('表名')->where(['id'=>['in',$province_ids],'status'=>1])->field('id,name')->order($exp)->select();

闭包查询,子查询

$list = Db::name('dict_region')->where('id','IN',function($query) use($where){

               $query->table('这里的表名要带前缀')->where($where)->field('province');

          })->field('id,name')->select();

order排序里面使用函数

$arr = Db::name('表名')

                ->alias('a')

                ->join('product_goods b','a.goods_id=b.id')

                ->where($where)

                ->group("a.goods_id")

                ->orderRaw("sum(a.publish_num) desc")   //将a表的publish_num的和作为排序的标准

                ->field("goods_id as id,b.name,sum(a.publish_num) as num")

                ->select();

wherOr的查询

$list = Db::name('message')

                ->alias('a')

                ->join('user b','a.user_id=b.id')

                ->where($where)

                ->where(function ($query) use ($keywords) {

                    $query->whereOr(['username'=>['like','%'.$keywords.'%']])   //昵称模糊搜索

                    ->whereOr(['nickname'=>['like','%'.$keywords.'%']]);    //昵称模糊搜索

                })

                ->order('a.id desc')

                ->select();