/**
* Builds a select query
*
* @param $table
* @param array $cols
* @param string $order
* @return \Aura\SqlQuery\Common\SelectInterface
*/
protected function buildSelectQuery($table, $criteria = array(), $cols = array('*'), $order = 'id ASC')
{
$select = $this->queryHandler->newSelect();
$select->cols($cols)->from($table)->orderBy(array($order));
foreach ($criteria as $column => $value) {
$select->where($column . ' = :' . $column);
}
if (!empty($criteria)) {
$select->bindValues($criteria);
}
return $select;
}
public function get_table_data_json($_post, $_table_config)
{
$query_factory = new QueryFactory('mysql');
#total records count
$select = $query_factory->newSelect();
$select->cols(array('COUNT(' . $_table_config['primary_column'] . ') AS total'))->from($_table_config['db_tablename']);
$result_set = $this->getReadConnection()->query($select->getStatement());
$result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC);
$total_records = $result_set->fetchArray($result_set)['total'];
#filtered records count
if (strlen($_post['search']['value']) > 1) {
$search_string = strtoupper($_post['search']['value']);
foreach ($_table_config['columns'] as $c) {
$select->orWhere("UCASE(" . $c['db_field'] . ") LIKE '%" . $search_string . "%'");
}
$result_set = $this->getReadConnection()->query($select->getStatement());
$result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC);
$total_filtered_records = $result_set->fetchArray($result_set)['total'];
} else {
$total_filtered_records = $total_records;
}
#return data
$ret = array('draw' => $_post['draw'], 'recordsTotal' => $total_records, 'recordsFiltered' => $total_filtered_records, 'data' => array());
#page records
$cols = [];
foreach ($_table_config['columns'] as $c) {
$cols[] = $c['db_field'] . ' AS f' . $c['i'];
}
$select = $query_factory->newSelect();
$select->cols($cols)->from($_table_config['db_tablename'])->orderBy(array($_table_config['columns'][$_post['order'][0]['column']]['db_field'] . " " . $_post['order'][0]['dir']))->limit($_post['length'])->offset($_post['start']);
if (strlen($_post['search']['value']) > 1) {
foreach ($_table_config['columns'] as $c) {
$select->orWhere("UCASE(" . $c['db_field'] . ") LIKE '%" . $search_string . "%'");
}
}
$result_set = $this->getReadConnection()->query($select->getStatement());
$result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC);
$data = $result_set->fetchAll();
foreach ($data as $row) {
$data_row = [];
foreach ($_table_config['columns'] as $c) {
$data_row[] = $row['f' . $c['i']];
}
$ret['data'][] = $data_row;
}
return json_encode($ret);
}
/**
* Returns a new SELECT object.
* @param string $tableName
* @param array $cols
* @return Common\SelectInterface
*/
public function newSelect($tableName = null, $cols = ['*'])
{
$select = parent::newSelect();
if (null !== $tableName) {
$select->from($tableName);
}
if (null !== $cols) {
$select->cols($cols);
}
return $select;
}
/**
* @return SelectInterface
*/
public static function newSelect()
{
return self::$queryFactory->newSelect();
}
public function select(string $table, $columns = ['*'])
{
$select = $this->query_factory->newSelect();
$select->from($table)->cols($columns);
return $select;
}
/**
* return select object
*
* @return \Aura\SqlQuery\Common\SelectInterface
*/
public function select()
{
return $this->_factory->newSelect();
}
public function composeCountQuery()
{
$queryFactory = new QueryFactory('mysql');
$select = $queryFactory->newSelect();
$matchString = $this->getMatchString();
$select->from($this->table)->cols(['COUNT(*)'])->where($matchString);
$select = $this->addWhereConditions($select);
return (string) $select;
}
