public function query($options = [])
{
$where = null;
if (!empty($options['where'])) {
$model = factory::model($options['model']);
$db = $model->db_object();
$where = 'AND ' . $db->prepare_condition($options['where']);
}
if (!empty($options['orderby']['full_text_search']) && !empty($options['where']['full_text_search,fts'])) {
$temp = [];
foreach ($options['orderby'] as $k => $v) {
if ($k != 'full_text_search') {
$temp[$k] = $v;
} else {
$model = factory::model($options['model']);
$db = $model->db_object();
$temp2 = $db->full_text_search_query($options['where']['full_text_search,fts']['fields'], $options['where']['full_text_search,fts']['str']);
$temp[$temp2['orderby']] = $v;
}
}
$options['orderby'] = $temp;
} else {
unset($options['orderby']['full_text_search']);
}
$options['orderby'] = !empty($options['orderby']) ? 'ORDER BY ' . array_key_sort_prepare_keys($options['orderby'], true) : '';
return <<<TTT
\t\t\tSELECT
\t\t\t\t*
\t\t\tFROM [table[{$options['model']}]] a
\t\t\tWHERE 1=1
\t\t\t\t{$where}
\t\t\t{$options['orderby']}
\t\t\tLIMIT {$options['limit']}
\t\t\tOFFSET {$options['offset']}
TTT;
}
/**
* Process details
*
* @param array $details
* @param array $parent_rows
* @param array $options
* @param array $parent_keys
* @param array $parent_types
* @param array $parent_settings
*/
private function process_details(&$details, &$parent_rows, $options, $parent_keys = [], $parent_types = [], $parent_maps = [], $parent_settings = [])
{
foreach ($details as $k => $v) {
$details[$k]['model_object'] = $model = factory::model($k, true);
$pk = $v['pk'] ?? $model->pk;
// generate keys from parent array
$keys = [];
$key_level = count($v['map']);
if ($key_level == 1) {
$k1 = key($v['map']);
$v1 = $v['map'][$k1];
$column = $v1;
} else {
$column = "concat_ws('::'[comma] " . implode('[comma] ', $v['map']) . ")";
}
// special array for keys
$parent_keys2 = $parent_keys;
$parent_keys2[] = $k;
$parent_types2 = $parent_types;
$parent_types2[] = $v['type'];
$parent_maps2 = $parent_maps;
$parent_maps2[] = $v['map'];
// create empty arrays
$result_keys = [];
$this->get_all_child_keys($parent_rows, $parent_maps2, $parent_keys2, $parent_types2, $result_keys, $keys);
foreach ($result_keys as $k0 => $v0) {
array_key_set($parent_rows, $v0, []);
}
// if we have relation
$sql_relation_join = '';
$sql_relation_columns = '';
if (!empty($v['__relation_pk'])) {
$temp3 = [];
foreach ($v['map'] as $k3 => $v3) {
$temp3[] = "b2.{$k3} = b.{$v3}";
}
$sql_relation_join = ' INNER JOIN ' . $this->primary_model->name . ' b2 ON ' . implode(' AND ', $temp3);
$sql_relation_columns = ', ' . implode(',', $v['__relation_pk']);
}
// sql extensions
$v['sql']['where'] = $v['sql']['where'] ?? null;
// building SQL
$sql = ' AND ' . $this->primary_model->db_object->prepare_condition([$column => $keys]);
$sql_full = 'SELECT b.*' . $sql_relation_columns . ' FROM ' . $model->name . ' b ' . $sql_relation_join . ' WHERE 1=1' . $sql . ($v['sql']['where'] ? ' AND ' . $v['sql']['where'] : '');
// order by
$orderby = $options['orderby'] ?? (!empty($model->orderby) ? $model->orderby : null);
if (!empty($orderby)) {
$sql_full .= ' ORDER BY ' . array_key_sort_prepare_keys($orderby, true);
}
// if we need to lock rows
if (!empty($options['for_update'])) {
$sql_full .= ' FOR UPDATE';
}
// quering
$result = $this->primary_model->db_object->query($sql_full, null);
// important not to set pk
if (!$result['success']) {
throw new Exception(implode(", ", $result['error']));
}
// if we got rows
if (!empty($result['rows'])) {
$reverse_map = array_reverse($parent_maps2, true);
foreach ($result['rows'] as $k2 => $v2) {
$master_key = [];
// entry itself
if ($v['type'] == '1M') {
$temp = [];
foreach ($pk as $v0) {
$temp[] = $v2[$v0];
}
$master_key[] = implode('::', $temp);
}
$previous = $v2;
foreach ($reverse_map as $k3 => $v3) {
$temp = [];
if (empty($v['__relation_pk'])) {
foreach ($v3 as $k4 => $v4) {
$previous[$k4] = $previous[$v4];
$temp[] = $previous[$v4];
}
} else {
foreach ($v['__relation_pk'] as $k4 => $v4) {
$temp[] = $previous[$v4];
}
}
array_unshift($master_key, $parent_keys2[$k3]);
if (($parent_types2[$k3 - 1] ?? '') != '11') {
array_unshift($master_key, implode('::', $temp));
}
}
array_key_set($parent_rows, $master_key, $v2);
}
// if we have more details
if (!empty($v['details'])) {
$this->process_details($v['details'], $parent_rows, $options, $parent_keys2, $parent_types2, $parent_maps2, $v);
}
}
}
}
/**
* Sort an array by certain keys with certain methods
*
* @param array $arr
* @param array $keys
* ['id' => SORT_ASC, 'name' => SORT_DESC]
* ['id' => 'asc', 'name' => 'desc']
* @param array $methods
* ['id' => SORT_NUMERIC, 'name' => SORT_NATURAL]
*/
function array_key_sort(&$arr, $keys, $methods = [])
{
$keys = array_key_sort_prepare_keys($keys, false);
// prepare a single array of parameters for multisort function
$params = [];
foreach ($keys as $k => $v) {
$params[$k . '_column'] = [];
$params[$k . '_order'] = $v;
$params[$k . '_flags'] = $methods[$k] ?? SORT_REGULAR;
foreach ($arr as $k2 => $v2) {
$params[$k . '_column']['_' . $k2] = $v2[$k];
}
}
// calling multisort function
call_user_func_array('array_multisort', $params);
// create final array
$final = [];
foreach ($keys as $k => $v) {
foreach ($params[$k . '_column'] as $k2 => $v2) {
$k2 = substr($k2, 1);
if (!isset($final[$k2])) {
$final[$k2] = $arr[$k2];
}
}
}
$arr = $final;
}
/**
* Get data as an array of rows
*
* @param array $options
* no_cache - if we need to skip caching
* search - array of search condition
* where - array of where conditions
* orderby - array of columns to sort by
* pk - primary key to be used by query
* columns - if we need to get certain columns
* limit - set this integer if we need to limit query
* @return array
*/
public function get($options = [])
{
$data = [];
$this->acl_get_options = $options;
// handle acl init
if (!empty($options['acl'])) {
$acl_key = get_called_class();
if (factory::model('object_acl_class', true)->acl_init($acl_key, $data, $this->acl_get_options) === false) {
return $data;
}
$options = $this->acl_get_options;
}
$options_query = [];
// if we are caching
if (!empty($this->cache) && empty($options['no_cache'])) {
$options_query['cache'] = true;
}
$options_query['cache_tags'] = !empty($this->cache_tags) ? array_values($this->cache_tags) : [];
$options_query['cache_tags'][] = $this->name;
$sql = '';
// pk
$pk = array_key_exists('pk', $options) ? $options['pk'] : $this->pk;
// preset columns
if (!empty($options['__preset'])) {
$columns = 'DISTINCT ';
if (!empty($pk) && count($pk) > 1) {
$temp = $pk;
unset($temp[array_search('preset_value', $temp)]);
$columns .= $this->db_object->prepare_expression($temp) . ', ';
}
$columns .= "concat_ws(' ', " . $this->db_object->prepare_expression($options['columns']) . ") preset_value";
$sql .= ' AND coalesce(' . $this->db_object->prepare_expression($options['columns']) . ') IS NOT NULL';
// if its a preset we cache
$options_query['cache'] = true;
} else {
// regular columns
if (!empty($options['columns'])) {
$columns = $this->db_object->prepare_expression($options['columns']);
} else {
$columns = '*';
}
}
// where
$sql .= !empty($options['where']) ? ' AND ' . $this->db_object->prepare_condition($options['where']) : '';
$sql .= !empty($options['search']) ? ' AND (' . $this->db_object->prepare_condition($options['search'], 'OR') . ')' : '';
// order by
$orderby = $options['orderby'] ?? (!empty($this->orderby) ? $this->orderby : null);
if (!empty($orderby)) {
$sql .= ' ORDER BY ' . array_key_sort_prepare_keys($orderby, true);
}
// limit
if (!empty($options['limit'])) {
$sql .= ' LIMIT ' . $options['limit'];
} else {
if (!empty($this->limit)) {
$sql .= ' LIMIT ' . $this->limit;
}
}
// querying
$sql_full = 'SELECT ' . $columns . ' FROM ' . $this->name . ' a WHERE 1=1' . $sql;
// memory caching
if ($this->cache_memory) {
// hash is query + primary key
$crypt = new crypt();
$sql_hash = $crypt->hash($sql_full . serialize($pk));
if (isset(cache::$memory_storage[$sql_hash])) {
return cache::$memory_storage[$sql_hash];
}
}
$result = $this->db_object->query($sql_full, $pk, $options_query);
if (!$result['success']) {
throw new Exception(implode(", ", $result['error']));
}
if ($this->cache_memory) {
cache::$memory_storage[$sql_hash] =& $result['rows'];
}
// single row
if (!empty($options['single_row'])) {
$data = current($result['rows']);
} else {
$data = $result['rows'];
}
// handle acl init
if (!empty($options['acl'])) {
if (factory::model('object_acl_class', true)->acl_finish($acl_key, $data, $this->acl_get_options) === false) {
return $data;
}
}
return $data;
}
