join

特点，需要处理好重名字段和字段筛选，查询出来的数据是同一级的。

sql语句

[-03-10T14:13:43+08:00][sql] SELECT `g`.*,c.name as cat_name FROM `ns_goods` `g` INNER JOIN `ns_category` `c` ON `g`.`category_id`=`c`.`id` WHERE `g`.`is_delete` = '0' AND `c`.`name` LIKE '%联想电脑%' ORDER BY `id` DESC LIMIT 0,5 [ RunTime:0.000980s ]

代码

还有搭配的搜索功能

//接收参数$params = input();//搜索条件$where = [];$where[] = ['c.is_delete', '=', 0];if (!empty($params['username'])) {$where[] = ['u.username', 'like', "%{$params['username']}%"];}if (!empty($params['goods_name'])) {$where[] = ['g.name', 'like', "%{$params['goods_name']}%"];}$data = Comment::alias('c')->join('user u', 'c.user_id = u.id')->join('goods g', 'c.goods_id = g.id')->field('c.*,u.username,g.name as goods_name')->where($where)->order('c.id desc')->paginate(5);return ok($data);

查询结果

{"code": 200,"msg": "","data": {"total": 6,"per_page": 5,"current_page": 1,"last_page": 2,"data": [{"id": 93,"category_id": 21,"name": "项文","desc": "Quam et voluptatem saepe sed voluptatum iusto quos. Minus quia ipsum impedit ut. Est magni consequatur praesentium ea minima.","imgs": ["https://placeholder.pics/svg/80x80/FF2030/FFF/房敏静.png","https://placeholder.pics/svg/80x80/FF2030/FFF/邹振国.png"],"stock": 1067,"shelf": 1,"sell_price": 1506,"is_delete": 0,"create_time": "-03-06 22:19:01","update_time": "-03-06 22:19:01","cat_name": "联想电脑配件"},{"id": 68,"category_id": 11,"name": "华浩","desc": "Molestiae a est et magni vel. Distinctio tenetur itaque expedita minus minus nihil. Consequatur incidunt mollitia accusantium velit animi corrupti.","imgs": ["https://placeholder.pics/svg/80x80/FF2030/FFF/闵正诚.png","https://placeholder.pics/svg/80x80/FF2030/FFF/米淑英.png"],"stock": 1811,"shelf": 1,"sell_price": 1487,"is_delete": 0,"create_time": "-03-06 22:19:01","update_time": "-03-06 22:19:01","cat_name": "联想电脑"},{"id": 67,"category_id": 21,"name": "解丽","desc": "Quasi dolorem veniam ut. Itaque modi qui maiores quidem unde. Et quis et ut voluptatem et enim dolorum. Molestiae qui voluptate reiciendis.","imgs": ["https://placeholder.pics/svg/80x80/FF2030/FFF/梁新华.png","https://placeholder.pics/svg/80x80/FF2030/FFF/宇鹰.png"],"stock": 1346,"shelf": 1,"sell_price": 1073,"is_delete": 0,"create_time": "-03-06 22:19:01","update_time": "-03-06 22:19:01","cat_name": "联想电脑配件"},{"id": 62,"category_id": 21,"name": "佟志勇","desc": "Voluptates porro omnis illum recusandae minima dignissimos. Animi a temporibus provident inventore sunt quos. Eum incidunt molestias cum a illum eveniet in. Ea et quo sit tempora iusto.","imgs": ["https://placeholder.pics/svg/80x80/FF2030/FFF/詹璐.png","https://placeholder.pics/svg/80x80/FF2030/FFF/方致远.png"],"stock": 1637,"shelf": 1,"sell_price": 1847,"is_delete": 0,"create_time": "-03-06 22:19:01","update_time": "-03-06 22:19:01","cat_name": "联想电脑配件"},{"id": 56,"category_id": 11,"name": "仲正诚","desc": "Earum sit qui iste illum. Ut odio quae facilis facere eos iure est reprehenderit. Quia voluptatem optio deleniti dolores inventore nulla. Voluptates vel quia unde et impedit illum in.","imgs": ["https://placeholder.pics/svg/80x80/FF2030/FFF/柳利.png","https://placeholder.pics/svg/80x80/FF2030/FFF/余淑珍.png"],"stock": 1442,"shelf": 1,"sell_price": 1491,"is_delete": 0,"create_time": "-03-06 22:19:01","update_time": "-03-06 22:19:01","cat_name": "联想电脑"}]}}

withJoin

withJoin和haswhere默认是inner join

publicfunction demo5(){$comm= Comment::withJoin("article")->where("article.title","like","%美%")->select()->toArray();}

sql语法：

SELECT*FROM`comment` `comment`INNERJOIN`article` `article`ON`comment`.`article_id`=`article`.`id`WHERE`article`.`title`LIKE'%美%'

hasWhere

注意，该方法，只支持单表，后面再跟一个haswhere是无效的，形式如下

hasWhere()->hasWhere()

不支持多个hasWhere连用是一大遗憾，在laravel中是支持的

代码

//接收参数$params = input();//搜索条件$where = [];//注意：这里的话需要指定以下表名，否则会出现条件模糊报错$where[] = ['goods.is_delete', '=', 0];//商品名称搜索if (!empty($params['name'])) {$where[] = ['goods.name', 'like', "%{$params['name']}%"];}$data = Goods::hasWhere('category', function ($query) use ($params) {if (!empty($params['cate_name'])) {$where[] = ['name', 'like', "%{$params['cate_name']}%"];}$query->where($where)->hidden(['is_delete']);})->hidden(['is_delete','category.is_delete'])->where($where)->with(['category'])->order('goods.id desc')->paginate(5);

生成的sql语句

[-03-10T19:44:22+08:00][sql] SELECT COUNT(*) AS think_count FROM `ns_goods` `Goods` INNER JOIN `ns_category` `Category` ON `Goods`.`category_id`=`Category`.`id` WHERE `Category`.`name` LIKE '%电脑%' AND `goods`.`is_delete` = '0' AND `goods`.`name` LIKE '%蓝帅%' LIMIT 1 [ RunTime:0.000817s ]

where in ids

可以每次都根据传递过来的参数进行单表查询，然后根据返回的id数组进行查找。

代码

//接收参数$params = input();//搜索条件$where = [];//注意：这里的话需要指定以下表名，否则会出现条件模糊报错$where[] = ['is_delete', '=', 0];//评论内容if (!empty($params['content'])) {$where[] = ['content', 'like', "%{$params['content']}%"];}if (!empty($params['username'])) {$res = User::where('username', 'like', "%{$params['username']}%")->where('is_delete', 0)->column('id');//如果不等于空if ($res) {$where[] = ['user_id', 'in', $res];}}if (!empty($params['goods_name'])) {$res = Goods::where('name', 'like', "%{$params['goods_name']}%")->where('is_delete', 0)->column('id');//如果不等于空if ($res) {$where[] = ['goods_id', 'in', $res];}}$data = Comment::where($where)->order('id desc')->withoutField(['is_delete'])->append(['username','goods_name'])->paginate(5);return ok($data);

结果

{"code": 200,"msg": "","data": {"total": 43,"per_page": 5,"current_page": 9,"last_page": 9,"data": [{"id": 4,"user_id": 24,"goods_id": 10,"content": "Pariatur dolores aut optio commodi ipsa nostrum laboriosam. Quaerat omnis eaque atque nemo. Autem vel quibusdam inventore voluptates eveniet et nisi.","create_time": "-03-14 20:20:18","update_time": "-03-14 20:20:18","username": "user24","goods_name": "柳淑华"},{"id": 3,"user_id": 2,"goods_id": 6,"content": "Ut voluptatem occaecati aut culpa natus nisi repellendus. In dolorem dolor rem perspiciatis ut. Aut reprehenderit sunt doloremque inventore voluptate adipisci quo.","create_time": "-03-14 20:20:18","update_time": "-03-14 20:20:18","username": "user2","goods_name": "衣鹏程"},{"id": 1,"user_id": 21,"goods_id": 21,"content": "Inventore dolor sunt error laborum ex omnis voluptatem. Esse iure omnis illo ut sint autem distinctio magni. Et est omnis pariatur quidem sequi. In ratione qui dolor consequatur aut.","create_time": "-03-14 20:20:18","update_time": "-03-14 20:20:18","username": "user21","goods_name": "霍晨"}]}}