private function buildQuery()
{
// init var
$parameters = array();
// construct the query in the controller instead of the model as an allowed exception for data grid usage
$query = 'SELECT p.id, p.email, p.display_name, p.status,
UNIX_TIMESTAMP(p.registered_on) AS registered_on FROM profiles AS p';
$where = array();
// add status
if (isset($this->filter['status'])) {
$where[] = 'p.status = ?';
$parameters[] = $this->filter['status'];
}
// add email
if (isset($this->filter['email'])) {
$where[] = 'p.email LIKE ?';
$parameters[] = '%' . $this->filter['email'] . '%';
}
// add group
if (isset($this->filter['group'])) {
$query .= ' INNER JOIN profiles_groups_rights AS pgr ON pgr.profile_id = p.id AND
(pgr.expires_on IS NULL OR pgr.expires_on > NOW())';
$where[] .= 'pgr.group_id = ?';
$parameters[] = $this->filter['group'];
}
// query
if (!empty($where)) {
$query .= ' WHERE ' . implode(' AND ', $where);
}
// group by profile (might have doubles because of the join on groups_rights)
$query .= ' GROUP BY p.id';
// query with matching parameters
return array($query, $parameters);
}