Last active
December 16, 2015 06:19
-
-
Save neo13/5390091 to your computer and use it in GitHub Desktop.
Laravel Administrator bundle / ModelHelper / getRows()
There was a problem in this method that stopped people to use this bundle with PostgreSQL, this is the modified version that helps you get over that problem
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Helper that builds a results array (with results and pagination info) | |
* It's same as getRows but it works for pgsql | |
* | |
* @param ModelConfig $config | |
* @param array $sort (with 'field' and 'direction' keys) | |
* @param array $filters (see Field::getFilters method for the value types) | |
*/ | |
public static function getRows($config, $sort = null, $filters = null) | |
{ | |
//grab the model instance | |
$model = $config->model; | |
//update the config sort options | |
$config->setSort($sort); | |
$sort = $config->sort; | |
//get things going by grouping the set | |
$query = $model::group_by($model->table().'.'.$model::$key); | |
//set up initial array states for the selects | |
//************* | |
// the problem originated here | |
// according to PostgreSQL if you want to select something you have to group your result by it (only if you using gruop by statement) | |
// you can either get id and then get the rest of fields using id or you can put all columns of table in group_by statement | |
// I chose the first approach | |
// so i'm selecting just the id and then i will get the rest of row using id | |
$selects = array(DB::raw($model->table().'.'.$model::$key)); | |
//************** | |
//then we set the filters | |
if ($filters && is_array($filters)) | |
{ | |
foreach ($filters as $filter) | |
{ | |
if (!$fieldObject = Field::get($filter['field'], $filter, $config)) | |
{ | |
continue; | |
} | |
$fieldObject->filterQuery($query, $model); | |
} | |
} | |
//determines if the sort should have the table prefixed to it | |
$sortOnTable = true; | |
//iterate over the columns to check if we need to join any values or add any extra columns | |
foreach ($config->columns['columns'] as $field => $column) | |
{ | |
//if this is a related column, we'll need to add some joins | |
$column->filterQuery($query, $selects, $model); | |
//if this is a related field or | |
if ( ($column->isRelated || $column->select) && $column->field === $sort['field']) | |
{ | |
$sortOnTable = false; | |
} | |
} | |
//if the sort is on the model's table, prefix the table name to it | |
if ($sortOnTable) | |
{ | |
$sort['field'] = $model->table() . '.' . $sort['field']; | |
} | |
/** | |
* We need to do our own pagination since there is a bug in the L3 paginator when using groupings :( | |
* When L4 is released, this problem will go away and we'll be able to use the paginator again | |
*/ | |
//first get the sql sans selects | |
$sql = $query->table->grammar->select($query->table); | |
//then we need to round out the inner select | |
$sql = "SELECT {$model->table()}.{$model::$key} " . $sql; | |
//then wrap the inner table and perform the count | |
$sql = "SELECT COUNT({$model::$key}) AS aggregate FROM ({$sql}) AS agg"; | |
//then perform the count query | |
$results = $query->table->connection->query($sql, $query->table->bindings); | |
$num_rows = $results[0]->aggregate; | |
$page = (int) \Input::get('page', 1); | |
$last = (int) ceil($num_rows / $config->rowsPerPage); | |
//if the current page is greater than the last page, set the current page to the last page | |
$page = $page > $last ? $last : $page; | |
//now we need to limit and offset the rows in remembrance of our dear lost friend paginate() | |
$query->take($config->rowsPerPage); | |
$query->skip($config->rowsPerPage * ($page === 0 ? $page : $page - 1)); | |
//order the set by the model table's id | |
$query->order_by($sort['field'], $sort['direction']); | |
//then retrieve the rows | |
$rows = $query->distinct()->get($selects); | |
//********* | |
// I also added this | |
// here i got the rest of fields | |
// I know you probebly asking why i didn't just join the query with table and be done with that | |
// But that way it whoud not match the rest of code and I didn't want to mess with the rest of code | |
$idPool = array(); | |
for ($i = 0; $i<count($rows); $i++) | |
{ | |
$idPool[$i] = $rows[$i]->key; | |
} | |
if( $idPool ) | |
{ | |
$rows = $model::where_in('id', $idPool)->get(); | |
} | |
//********* | |
$results = array(); | |
//convert the resulting set into arrays | |
foreach ($rows as $item) | |
{ | |
//iterate over the included and related columns | |
$onTableColumns = array_merge($config->columns['includedColumns'], $config->columns['relatedColumns']); | |
$arr = array(); | |
foreach ($onTableColumns as $field => $col) | |
{ | |
//if this column is in our objects array, render the output with the given value | |
if (isset($config->columns['columnObjects'][$field])) | |
{ | |
$arr[$field] = $config->columns['columnObjects'][$field]->renderOutput($item->get_attribute($field)); | |
} | |
//otherwise it's likely the primary key column which wasn't included (though it's needed for identification purposes) | |
else | |
{ | |
$arr[$field] = $item->get_attribute($field); | |
} | |
} | |
//then grab the computed, unsortable columns | |
foreach ($config->columns['computedColumns'] as $col) | |
{ | |
$arr[$col] = $config->columns['columnObjects'][$col]->renderOutput($item->{$col}); | |
} | |
$results[] = $arr; | |
} | |
return array( | |
'page' => $page, | |
'last' => $last, | |
'total' => $num_rows, | |
'results' => $results, | |
); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
for which version of administrator is this?
i installed it with:
"require": {
"frozennode/administrator": "dev-master"
},
In L4, but i don't have a ModelHelper.php