public function process(\SelectQuery $query)
{
$result = $query->limit($this->options['limit'])->offset($this->options['page'] * $this->options['limit'])->fetchAll();
$this->count = count($result);
// Check to see if total is equal to our limit and check for the real total
if ($this->options['page'] || $this->count == $this->options['limit']) {
$this->total = (int) $query->limit(null)->offset(null)->select(null)->select('COUNT(*)')->fetchColumn();
} else {
$this->total = $this->count;
}
return $result;
}
public static function getComments($table = false, $table_id = false, $limit = false)
{
$query = new SelectQuery('Comment');
$query->field(array('`comments`.*, `backend_users`.`username`, `backend_users`.`email`'))->leftJoin('BackendUser', '`comments`.`user_id` = `backend_users`.`id`')->filter('`comments`.`active` = 1')->order('IF(`comments`.`in_reply_to` = 0, `comments`.`id`, `comments`.`in_reply_to`) DESC');
$params = array();
if ($table) {
$query->filter('`comments`.`foreign_table` = :table');
$params[':table'] = $table;
}
if ($table_id) {
$query->filter('`comments`.`foreign_id` = :table_id');
$params[':table_id'] = $table_id;
}
if ($limit) {
$query->limit($limit);
}
return $query->fetchAll($params);
}
public function action_filter($pageId = 1)
{
$query = new SelectQuery('BackendRequest');
$query->setFields(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query', 'COUNT(id) AS `occured`', 'MAX(`added`) AS `last_occured`'));
$query->setGroup(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query'));
$params = $queryFilter = array();
$parameters = Controller::getVar('params');
$sort = Controller::getVar('sort');
if (!empty($parameters['userId'])) {
$queryFilter[] = 'user_id = :userId';
$params[':userId'] = $parameters['userId'];
}
if (!empty($parameters['query'])) {
$queryFilter[] = "query LIKE('%{$parameters['query']}%')";
}
if (!empty($parameters['ip'])) {
$queryFilter[] = "ip LIKE('%{$parameters['ip']}%')";
}
if (!empty($parameters['user_agent'])) {
$queryFilter[] = "user_agent LIKE('%{$parameters['user_agent']}%')";
}
$query->filter($queryFilter);
$count = 10;
if (!empty($sort['field'])) {
$query->setOrder(array($sort['field'] . ' ' . $sort['order']));
}
if ($pageId == 1) {
$start = 0;
} elseif ($pageId == 0) {
$start = false;
$count = false;
} else {
$start = floor(($pageId - 1) * $count);
}
$pager = array();
if ($start === 'all') {
$limit = 'all';
} else {
if ($start || $count) {
$limit = "{$start}, {$count}";
} else {
$limit = false;
}
}
$query->limit($limit);
$items = $query->fetchAll($params);
$totalItems = $query->getCount($params);
$pager = '';
if ($start || $count) {
$pager = array('currentPage' => $pageId, 'itemCount' => count($items), 'itemTotal' => $totalItems, 'totalPages' => round(($totalItems - 1) / $count, 0));
}
$retArray['pager'] = $pager;
$retArray['data'] = $items;
$retArray['params'] = $parameters;
$retArray['sort'] = $sort;
return $retArray;
}
/**
* @return SelectQuery
**/
public function fillSelectQuery(SelectQuery $query)
{
$query->limit($this->limit, $this->offset);
if ($this->distinct) {
$query->distinct();
}
if ($this->logic->getSize()) {
$query->andWhere($this->logic->toMapped($this->checkAndGetDao(), $query));
}
if ($this->order) {
$query->setOrderChain($this->order->toMapped($this->checkAndGetDao(), $query));
}
if ($this->projection->isEmpty() && $this->strategy->getId() != FetchStrategy::CASCADE) {
$this->joinProperties($query, $this->checkAndGetDao(), $this->checkAndGetDao()->getTable(), true);
}
return $query;
}
public function getSelectSQL($options = array())
{
//Check the DB Connection
$this->error_msg = false;
if (!$this->checkConnection()) {
if (class_exists('BackendError', false)) {
BackendError::add(get_class($this) . ': DB Connection Error', 'getSelectSQL');
}
$this->error_msg = 'DB Connection Error';
return false;
}
$mode = array_key_exists('mode', $options) ? $options['mode'] : 'list';
$query = new SelectQuery($this, array('connection' => $this->db));
//Fields
$fields = array_key_exists('fields', $options) ? $options['fields'] : array();
if (empty($fields)) {
$query->field("`{$this->meta['table']}`.*");
} else {
$query->field($fields);
}
//Joins
$joins = array_key_exists('joins', $options) ? $options['joins'] : array();
if (count($joins)) {
foreach ($joins as $join) {
if (is_array($join)) {
$query->joinArray($join);
}
}
}
$q_params = array();
if (!empty($options['conditions'])) {
$query->filter($options['conditions']);
}
//Mode specific
$limit = false;
switch ($mode) {
case 'object':
case 'array':
case 'full_object':
if (!empty($this->meta['id'])) {
$query->filter("`{$this->meta['table']}`.`{$this->meta['id_field']}` = :{$this->meta['table']}_id");
$q_params[":{$this->meta['table']}_id"] = $this->meta['id'];
} else {
$query->limit(empty($limit) ? 1 : $limit);
}
break;
case 'list':
if (array_key_exists('limit', $options) && $options['limit'] != 'all') {
$query->limit($options['limit']);
}
break;
}
//Parameters
if (array_key_exists('parameters', $options)) {
if (is_array($options['parameters'])) {
$q_params = array_merge($q_params, $options['parameters']);
} else {
$q_params[] = $options['parameters'];
}
} else {
if (!empty($this->meta['parameters'])) {
if (is_array($this->meta['parameters'])) {
$q_params = array_merge($q_params, $this->meta['parameters']);
} else {
$q_params[] = $parameters;
}
}
}
//Filters
if (array_key_exists('filters', $options)) {
$query->filter($options['filters']);
} else {
if (!empty($this->meta['filters'])) {
$query->filter($this->meta['filters']);
}
}
//Order
if (array_key_exists('order', $options)) {
$query->order($options['order']);
} else {
if (!empty($this->meta['order'])) {
$query->order($this->meta['order']);
}
}
//Group
if (array_key_exists('group', $options)) {
$query->group($options['group']);
} else {
if (!empty($this->meta['group'])) {
$query->group($this->meta['group']);
}
}
//Check Ownership
if (array_key_exists('owner_id', $this->meta['fields'])) {
if ($user = BackendUser::check()) {
if (!in_array('superadmin', $user->roles)) {
$query->filter("`{$this->meta['table']}`.`owner_id` = :owner_id");
$q_params[':owner_id'] = $user->id;
}
}
}
return array($query, $q_params);
}