Thinkphp5 的常用连式查询

[TOC]

取出表中改字符串前两位等于01的数据

$pepper_count = $this->orderModel->where("instr(order_id,'02')",2)->count('id');

按主键查询

按主键查用放在括号里

//把按商户和平台的id查出来的数据传到edit前台模板里
$id=input('id');
$agencyRes=db('merchant_agency')
    ->field('a.*,b.agency_name')
    ->alias('a')
    ->join('agency b','a.agency_id=b.id')
    ->find($id);

不按主键查

  $userInfo=db('user')

        ->field('a.*,b.group_id,c.title')

        ->alias('a')

        ->join('role_group_access b','a.uid=b.uid')

        ->join('role_group c','b.group_id=c.group_id')

        ->where('a.uid',$uid)

        ->find();

JOIN方法 的左右连接

JOIN方法也是连贯操作方法之一，用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

     $goods_list =  db('goods_menu_dishes')
            ->alias('g')
            ->join('agency_goods d',"g.menu_id = d.menu_id ",'left') //关联类型。可以为:INNER、LEFT、RIGHT、FULL，不区分大小写，默认为INNER。
            ->where("g.merchant_id ",$merchant_id)
            ->where('d.agency_id',$agency_id)
            ->where('g.menu_id',$menu_id)
            ->field("g.goods_sn,d.out_sku_id")
            ->select();
        var_dump($goods_list);
        die;        

not in 方法

$authGroupRes=db('role_group')
          ->field('group_id,title')
          ->where('group_id','not in','1,3,4')
          ->select();

like 查询

use think\Db;  //上面需要use
//取出广告栏里title里含有index_banner关键字并且设置为推荐的所有轮播图信息
// $indexModel=new indexModel;
$bannerRes=Db::table('bk_cate')
		    ->field('b.*,a.catename')
		    ->alias('a')
		    ->join('bk_article b','a.id = b.cateid')
		    ->where('catename','like','%轮播图%')
		    ->where('rec','eq',1)
		    ->limit(5)
		    ->select();

where 按条件筛选查询

if (isset($data['station_name']) && empty($data['station_name'])) {
                unset($data['station_name']);
            } else {
                $where['a.station_name'] = ['like', "%" . $data['station_name'] . "%"];
            }
            if (isset($data['erp_code']) && empty($data['erp_code'])) {
                unset($data['erp_code']);
            } else {
                $where['a.erp_code'] = ['=', $data['erp_code']];
            }
            if (isset($data['city']) && $data['city'] == '[2,25]') {
                $where['a.city'] = ['not in', $data['city']];
            } else {
                $where['a.city'] = ['=', $data['city']];
            }

            if (isset($where['a.erp_code']) && empty($where['a.erp_code'])) {
                unset($where['a.station_name']);
            }

            $data = $this->seachAgencyStationiInfo($where);

 public function seachAgencyStationiInfo($where = '')
    {
        //查出所有平台的信息展示出来,供开通平台选用
        $agencyInfo = db('agency')->where('display','=',1)->select();
        $this->assign('agencyInfo', $agencyInfo);

        //查出表格的前半部分显示数据
        $shopAgencyInfo = db('shop_station')
            ->field('a.station_id,a.merchant_id,a.daqu_id,a.erp_code,a.city,a.station_name,a.tel,b.region_name')
            ->alias('a')
            ->join('region b', 'a.city=b.region_id')
            ->where(isset($where) ? $where : '')
            // ->fetchSql()
            // ->order('station_id desc')
            ->order('city asc')
            ->paginate(20);
   }

更详情点下面连接
JOIN更多操作