话不多说,代码奉上:
控制器层:
$post = input("post.");
$Themaster = new Themaster;
$whereOr = '';
$where = '';
$search['search'] = '';
$search['loservice'] = '';
$search['state'] = '';
$search['addtime'] = '';
$search['updatetime'] = '';
if ($post) {
// 关键字模糊查询
if (trim($post['search'])) {
$whereOr[] = ['gzh_themaster.name|gzh_users.nickname|gzh_themaster.thephone|gzh_themaster.type|gzh_themaster.scope',"like","%".trim($post['search'])."%"];
$search['search'] = $post['search'];
}
// 工龄查询
if (trim($post['loservice'])) {
$where[] = ['gzh_themaster.loservice',"=",trim($post['loservice'])];
$search['loservice'] = $post['loservice'];
}
// 师傅状态查询
if (trim($post['state'])) {
$where[] = ['gzh_themaster.state',"=",trim($post['state'])];
$search['state'] = $post['state'];
}
// 申请时间查询(区间查询)
if (trim($post['addtime'])) {
$addtime = strtotime(trim($post['addtime']));
$where[] = ['gzh_themaster.addtime',[">=",$addtime],["<",$addtime+86400] ,"AND"];
$search['addtime'] = $post['addtime'];
}
// 申请通过时间查询(区间查询)
if (trim($post['updatetime'])) {
$updatetime = strtotime(trim($post['updatetime']));
$where[] = ['gzh_themaster.updatetime',[">=",$updatetime],["<",$updatetime+86400] ,"AND"];
$search['updatetime'] = $post['updatetime'];
}
}
$themaster_list = $Themaster->themasterList($whereOr,$where);
$this->assign('search',$search);
$this->assign('themaster_list',$themaster_list);
return $this->fetch();
模型层:
public function themasterList($whereOr = array(),$where = array())
{
$themaster = $this
// AND查询
->where($where)
// OR 查询
->where($whereOr)
->field('
gzh_themaster.*,
gzh_users.nickname,
gzh_users.sex,
gzh_users.city,
gzh_users.phone,
gzh_users.type as user_type,
gzh_users.headimgurl,
gzh_users.thumbpic'
)
->join('gzh_users',"gzh_users.id = gzh_themaster.uid")
->order('id desc')
->paginate(4,false);
return $themaster;
}
生成的SQL语句:
SELECT
`gzh_themaster`.*,
`gzh_users`.`nickname`,
`gzh_users`.`sex`,
`gzh_users`.`city`,
`gzh_users`.`phone`,
gzh_users.type AS user_type,
`gzh_users`.`headimgurl`,
`gzh_users`.`thumbpic`
FROM
`gzh_themaster`
INNER JOIN `gzh_users` `gzh_users` ON
`gzh_users`.`id` = `gzh_themaster`.`uid`
WHERE
`gzh_themaster`.`loservice` = '1-3年'
AND `gzh_themaster`.`state` = '1'
AND `gzh_themaster`.`addtime` >= '1545580800'
AND `gzh_themaster`.`addtime` < 1545667200
AND `gzh_themaster`.`updatetime` >= '1545580800'
AND `gzh_themaster`.`updatetime` < 1545667200
AND(
`gzh_themaster`.`name` LIKE '%99977%'
OR `gzh_users`.`nickname` LIKE '%99977%'
OR `gzh_themaster`.`thephone` LIKE '%99977%'
OR `gzh_themaster`.`type` LIKE '%99977%'
OR `gzh_themaster`.`scope` LIKE '%99977%'
)
ORDER BY
id
DESC