我有一个users表、一个role_members表和一个roles表。
role_members表有一个user_id和一个role_id,因为用户可以有许多角色。
我有一个DataTable,里面有用户id、电子邮件、姓名和角色。我可以很容易地根据users表中的字段进行排序和筛选。按照DataTable的工作方式,有一个搜索框可以搜索所有字段。所以,我的搜索查询需要是一个"OR",其中用户名像search_term,或者电子邮件像search_torm,或者角色名像search_trm
当我尝试根据角色名称进行排序或筛选时,就会出现问题如果我想按RoleMember.Role.name排序或按RoleMember_Role.name搜索,我该怎么做
我的查找选项如下:
$sOrder = array('User.full_name' => 'asc');
if (isset($params['iSortCol_0'])) {
$sOrder = array();
for ( $i=0 ; $i<intval( $params['iSortingCols'] ) ; $i++ )
{
if ( $params[ 'bSortable_'.intval($params['iSortCol_'.$i]) ] == "true" )
{
$sOrder[$columns[ intval( $params['iSortCol_'.$i] ) ]] = $params['sSortDir_'.$i] ;
}
}
}
$find_options = array(
'order' => $sOrder,
'contain' => array(
'RoleMember' => array('Role', 'Instance'),
'AllowedLocation'
),
'limit' => $limit,
'offset' => $offset
);
if(isset($params['sSearch'])){
$search_term = '%'. $params['sSearch'] .'%';
$find_options['conditions'] = array(
'Or' => array(
'User.full_name LIKE ' => $search_term,
'User.email LIKE ' => $search_term,
'User.username LIKE ' => $search_term
//'RoleMember.Role.name LIKE ' => $search_term,
)
);
}
Joins肯定会起作用,但如果不缓存查询,效率会非常低。。。现在试试这个-
$options['joins'] = array(
array(
'table' => 'role_members',
'alias' => 'RoleMember',
'type' => 'INNER',
'conditions' => array(
'RoleMember.user_id = User.id'
)
),
array(
'table' => 'roles',
'alias' => 'Role',
'type' => 'INNER',
'conditions' => array(
'RoleMember.role_id = Role.id'
)
)
); // here we join all the tables, so all fields are available to place conditions...
$options['conditions'] = array(
'OR' => array(
'User.full_name LIKE ' => $search_term,
'User.email LIKE ' => $search_term,
'User.username LIKE ' => $search_term
'Role.name LIKE ' => $search_term,
)
);
$users = $this->User->find('all', $options);