/** * 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); }