/**
* wrapper for custom find queries
* @param array $filter
* @param array $options
* @param int $ttl
* @param bool $count
* @return array|false array of underlying cursor objects
*/
protected function filteredFind($filter = NULL, array $options = NULL, $ttl = 0, $count = false)
{
if ($this->grp_stack) {
if ($this->dbsType == 'mongo') {
$group = array('keys' => $this->grp_stack['keys'], 'reduce' => 'function (obj, prev) {' . $this->grp_stack['reduce'] . '}', 'initial' => $this->grp_stack['initial'], 'finalize' => $this->grp_stack['finalize']);
if ($options && isset($options['group'])) {
if (is_array($options['group'])) {
$options['group'] = array_merge($options['group'], $group);
} else {
$keys = explode(',', $options['group']);
$keys = array_combine($keys, array_fill(0, count($keys), 1));
$group['keys'] = array_merge($group['keys'], $keys);
$options['group'] = $group;
}
} else {
$options = array('group' => $group);
}
}
if ($this->dbsType == 'sql') {
if ($options && isset($options['group'])) {
$options['group'] .= ',' . $this->grp_stack;
} else {
$options['group'] = $this->grp_stack;
}
}
// Jig can't group yet, but pending enhancement https://github.com/bcosca/fatfree/pull/616
}
if ($this->dbsType == 'sql' && !$count) {
$m_refl = new \ReflectionObject($this->mapper);
$m_ad_prop = $m_refl->getProperty('adhoc');
$m_ad_prop->setAccessible(true);
$m_refl_adhoc = $m_ad_prop->getValue($this->mapper);
$m_ad_prop->setAccessible(false);
unset($m_ad_prop, $m_refl);
}
$hasJoin = array();
if ($this->hasCond) {
foreach ($this->hasCond as $key => $hasCond) {
$addToFilter = null;
if ($deep = is_int(strpos($key, '.'))) {
$key = rtrim($key, '.');
$hasCond = array(null, null);
}
list($has_filter, $has_options) = $hasCond;
$type = $this->fieldConf[$key]['relType'];
$fromConf = $this->fieldConf[$key][$type];
switch ($type) {
case 'has-one':
case 'has-many':
if (!is_array($fromConf)) {
trigger_error(sprintf(self::E_REL_CONF_INC, $key));
}
$id = $this->dbsType == 'sql' ? $this->primary : '_id';
if ($type == 'has-many' && isset($fromConf['relField']) && $fromConf['hasRel'] == 'belongs-to-one') {
$id = $fromConf['relField'];
}
// many-to-many
if ($type == 'has-many' && $fromConf['hasRel'] == 'has-many') {
if (!$deep && $this->dbsType == 'sql' && !isset($has_options['limit']) && !isset($has_options['offset'])) {
$hasJoin = array_merge($hasJoin, $this->_hasJoinMM_sql($key, $hasCond, $filter, $options));
$options['group'] = (isset($options['group']) ? $options['group'] . ',' : '') . $this->db->quotekey($this->table . '.' . $this->primary);
$groupFields = explode(',', preg_replace('/"/', '', $options['group']));
// all non-aggregated fields need to be present in the GROUP BY clause
if (isset($m_refl_adhoc) && preg_match('/sybase|dblib|odbc|sqlsrv/i', $this->db->driver())) {
foreach (array_diff($this->mapper->fields(), array_keys($m_refl_adhoc)) as $field) {
if (!in_array($this->table . '.' . $field, $groupFields)) {
$options['group'] .= ', ' . $this->db->quotekey($this->table . '.' . $field);
}
}
}
} elseif ($result = $this->_hasRefsInMM($key, $has_filter, $has_options, $ttl)) {
$addToFilter = array($id . ' IN ?', $result);
}
} elseif ($result = $this->_hasRefsIn($key, $has_filter, $has_options, $ttl)) {
$addToFilter = array($id . ' IN ?', $result);
}
break;
// one-to-*
// one-to-*
case 'belongs-to-one':
if (!$deep && $this->dbsType == 'sql' && !isset($has_options['limit']) && !isset($has_options['offset'])) {
if (!is_array($fromConf)) {
$fromConf = array($fromConf, '_id');
}
$rel = $fromConf[0]::resolveConfiguration();
if ($this->dbsType == 'sql' && $fromConf[1] == '_id') {
$fromConf[1] = $rel['primary'];
}
$hasJoin[] = $this->_hasJoin_sql($key, $rel['table'], $hasCond, $filter, $options);
} elseif ($result = $this->_hasRefsIn($key, $has_filter, $has_options, $ttl)) {
$addToFilter = array($key . ' IN ?', $result);
}
break;
default:
trigger_error(self::E_HAS_COND);
}
if (isset($result) && !isset($addToFilter)) {
return false;
} elseif (isset($addToFilter)) {
if (!$filter) {
$filter = array('');
}
if (!empty($filter[0])) {
$filter[0] .= ' and ';
}
$cond = array_shift($addToFilter);
if ($this->dbsType == 'sql') {
$cond = $this->_sql_quoteCondition($cond, $this->db->quotekey($this->getTable()));
}
$filter[0] .= '(' . $cond . ')';
$filter = array_merge($filter, $addToFilter);
}
}
$this->hasCond = null;
}
$filter = $this->queryParser->prepareFilter($filter, $this->dbsType, $this->fieldConf);
if ($this->dbsType == 'sql') {
$qtable = $this->db->quotekey($this->table);
if (isset($options['order']) && $this->db->driver() == 'pgsql') {
// PostgreSQLism: sort NULL values to the end of a table
$options['order'] = preg_replace('/\\h+DESC/i', ' DESC NULLS LAST', $options['order']);
}
if (!empty($hasJoin)) {
// assemble full sql query
$adhoc = '';
if ($count) {
$sql = 'SELECT COUNT(*) AS ' . $this->db->quotekey('rows') . ' FROM ' . $qtable;
} else {
if (!empty($this->preBinds)) {
$crit = array_shift($filter);
$filter = array_merge($this->preBinds, $filter);
array_unshift($filter, $crit);
}
if (!empty($m_refl_adhoc)) {
foreach ($m_refl_adhoc as $key => $val) {
$adhoc .= ', ' . $val['expr'] . ' AS ' . $key;
}
}
$sql = 'SELECT ' . $qtable . '.*' . $adhoc . ' FROM ' . $qtable;
}
$sql .= ' ' . implode(' ', $hasJoin) . ' WHERE ' . $filter[0];
if (!$count) {
if (isset($options['group'])) {
$sql .= ' GROUP BY ' . $this->_sql_quoteCondition($options['group'], $this->table);
}
if (isset($options['order'])) {
$sql .= ' ORDER BY ' . $options['order'];
}
if (preg_match('/mssql|sqlsrv|odbc/', $this->db->driver()) && (isset($options['limit']) || isset($options['offset']))) {
$ofs = isset($options['offset']) ? (int) $options['offset'] : 0;
$lmt = isset($options['limit']) ? (int) $options['limit'] : 0;
if (strncmp($this->db->version(), '11', 2) >= 0) {
// SQL Server 2012
if (!isset($options['order'])) {
$sql .= ' ORDER BY ' . $this->db->quotekey($this->primary);
}
$sql .= ' OFFSET ' . $ofs . ' ROWS' . ($lmt ? ' FETCH NEXT ' . $lmt . ' ROWS ONLY' : '');
} else {
// SQL Server 2008
$order = !isset($options['order']) ? $this->db->quotekey($this->table . '.' . $this->primary) : $options['order'];
$sql = str_replace('SELECT', 'SELECT ' . ($lmt > 0 ? 'TOP ' . ($ofs + $lmt) : '') . ' ROW_NUMBER() ' . 'OVER (ORDER BY ' . $order . ') AS rnum,', $sql);
$sql = 'SELECT * FROM (' . $sql . ') x WHERE rnum > ' . $ofs;
}
} else {
if (isset($options['limit'])) {
$sql .= ' LIMIT ' . (int) $options['limit'];
}
if (isset($options['offset'])) {
$sql .= ' OFFSET ' . (int) $options['offset'];
}
}
}
unset($filter[0]);
$result = $this->db->exec($sql, $filter, $ttl);
if ($count) {
return $result[0]['rows'];
}
foreach ($result as &$record) {
// factory new mappers
$mapper = clone $this->mapper;
$mapper->reset();
// TODO: refactor this. Reflection can be removed for F3 >= v3.4.1
$mapper->query = array($record);
$m_adhoc = empty($adhoc) ? array() : $m_refl_adhoc;
foreach ($record as $key => $val) {
if (isset($m_refl_adhoc[$key])) {
$m_adhoc[$key]['value'] = $val;
} else {
$mapper->set($key, $val);
}
}
if (!empty($adhoc)) {
$refl = new \ReflectionObject($mapper);
$prop = $refl->getProperty('adhoc');
$prop->setAccessible(true);
$prop->setValue($mapper, $m_adhoc);
$prop->setAccessible(false);
}
$record = $mapper;
unset($record, $mapper);
}
return $result;
} elseif (!empty($this->preBinds) && !$count) {
// bind values to adhoc queries
if (!$filter) {
// we (PDO) need any filter to bind values
$filter = array('1=1');
}
$crit = array_shift($filter);
$filter = array_merge($this->preBinds, $filter);
array_unshift($filter, $crit);
}
}
return $count ? $this->mapper->count($filter, $ttl) : $this->mapper->find($filter, $this->queryParser->prepareOptions($options, $this->dbsType), $ttl);
}
protected function filteredFind($filter = NULL, array $options = NULL, $ttl = 0)
{
if ($this->hasCond) {
$hasJoin = array();
foreach ($this->hasCond as $key => $hasCond) {
$addToFilter = null;
list($has_filter, $has_options) = $hasCond;
$type = $this->fieldConf[$key]['relType'];
$fromConf = $this->fieldConf[$key][$type];
switch ($type) {
case 'has-one':
case 'has-many':
if (!is_array($fromConf)) {
trigger_error(sprintf(self::E_REL_CONF_INC, $key));
}
if ($type == 'has-many' && $fromConf['hasRel'] == 'has-many') {
if ($this->dbsType == 'sql' && !isset($has_options['limit']) && !isset($has_options['offset'])) {
$hasJoin = array_merge($hasJoin, $this->_hasJoinMM_sql($key, $hasCond, $filter, $options));
} elseif ($result = $this->_hasRefsInMM($key, $has_filter, $has_options, $ttl)) {
$addToFilter = array('_id IN ?', $result);
}
} elseif ($result = $this->_hasRefsIn($key, $has_filter, $has_options, $ttl)) {
$addToFilter = array('_id IN ?', $result);
}
break;
case 'belongs-to-one':
if ($this->dbsType == 'sql' && !isset($has_options['limit']) && !isset($has_options['offset'])) {
$rel = $fromConf::resolveConfiguration();
$hasJoin[] = $this->_hasJoin_sql($key, $rel['table'], $hasCond, $filter, $options);
} elseif ($result = $this->_hasRefsIn($key, $has_filter, $has_options, $ttl)) {
$addToFilter = array($key . ' IN ?', $result);
}
break;
default:
trigger_error(self::E_HAS_COND);
}
if (isset($result) && !isset($addToFilter)) {
return false;
} elseif (isset($addToFilter)) {
if (!$filter) {
$filter = array('');
}
if (!empty($filter[0])) {
$filter[0] .= ' and ';
}
$filter[0] .= '(' . array_shift($addToFilter) . ')';
$filter = array_merge($filter, $addToFilter);
}
}
$this->hasCond = null;
if (!empty($hasJoin)) {
// assemble full query
$filter = $this->queryParser->prepareFilter($filter, $this->dbsType);
$qtable = $this->db->quotekey($this->table);
$sql = 'SELECT ' . $qtable . '.* FROM ' . $qtable;
foreach ($hasJoin as $q) {
$sql .= ' ' . $q;
}
$sql .= ' WHERE ' . $filter[0];
if (isset($options['group'])) {
$sql .= ' GROUP BY ' . $options['group'];
}
unset($filter[0]);
$result = $this->db->exec($sql, $filter, $ttl);
foreach ($result as &$record) {
$mapper = clone $this->mapper;
$mapper->reset();
foreach ($record as $key => $val) {
$mapper->set($key, $val);
}
$record = $mapper;
unset($record, $mapper);
}
return $result;
}
}
$filter = $this->queryParser->prepareFilter($filter, $this->dbsType);
$options = $this->queryParser->prepareOptions($options, $this->dbsType);
return $this->mapper->find($filter, $options, $ttl);
}