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; }