SQL Server throws invalid column error on select list

[edgji]

In Laravel 4.1 I managed to set up administrator with a model for a table I replicated from SQL Server into MySQL for basic testing and setup. I got to the point where I needed to have this running on the existing SQL server data so I tried to connecting but I'm getting the following error

Column 'COLUMN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (SQL: SELECT COUNT(id) AS aggregate FROM (select [TABLENAME].* from [TABLENAME] group by [TABLENAME].[id]) AS agg) (Bindings: array ( ))

*(COLUMN and TABLENAME replace their respective actual property names)

[janhartigan]

Unfortunately I have no access to SQL Server, so I can't debug this. I suspect this is happening because SQL Server doesn't like having a GROUP BY clause when there is no grouping function. This is the offending bit of code:

https://github.com/FrozenNode/Laravel-Administrator/blob/master/src/Frozennode/Administrator/DataTable/DataTable.php#L193

Could you try running this same query but with a grouping function on the subselect?

[edgji]

It took me a while but I hacked my way through the issue last night. In reference to http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html I figured SQL server was more strict with the formatting of the GROUP BY clause and ended up appending each of the columns to the GROUP BY which ends up working.

[pablomaurer]

Can you show how you solved it? I'm having the same problem..
how far i came: http://stackoverflow.com/questions/22837318/laravel-4-query-groupby-all-columns-from-table

[pablomaurer]

First make a BaseModel.php ..so i can get all columns names from all tables.

<?php
// New File
class BaseModel extends \Eloquent {

    public static function getTableName()
    {
        return with(new static)->getTable();
    }

    public function getColumnsNames() {
        $table = with(new static)->getTable();

        $connection = DB::connection();
        $connection->getSchemaBuilder();
        //$s = DB::getSchmema();

        $grammar = $connection->getSchemaGrammar();
        $table = $connection->getTablePrefix().$table;
        $results = $connection->select($grammar->compileColumnExists($table));
        return array_unique($connection->getPostProcessor()->processColumnListing($results));
    }
}

Second All models have to extend from BaseModel... instead of \Eloquent
Third in DataTable.php :

//grab the model instance
$model = $this->config->getDataModel();
$cols = $model->getColumnsNames(); // ------> New Line around Line 110

// ----> at line 119/120 instead of  $query = $model->groupBy($table . '.' . $keyName);
foreach ($cols as $col) {
    $model = $model->groupBy($table.'.'.$col);
}
$query = $model;

And then some Pages work an other ar saying
SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Argument data type int is invalid for argument 2 of format function. (SQL: select distinct top 20 [l_theaters].*, (SELECT COUNT(num_films_l_films.id) FROM l_theaters AS num_films_l_theaters LEFT JOIN l_films_theaters AS num_films_l_films_theaters ON num_films_l_films_theaters.theater_id = num_films_l_theaters.id LEFT JOIN l_films AS num_films_l_films ON num_films_l_films.id = num_films_l_films_theaters.film_id WHERE l_theaters.id = num_films_l_films_theaters.theater_id) AS [num_films], (SELECT CONCAT('$', FORMAT(SUM(box_office_l_box_office.revenue), 2)) FROM l_box_office AS box_office_l_box_office WHERE l_theaters.id = box_office_l_box_office.theater_id) AS [box_office] from [l_theaters] group by [l_theaters].[id], [l_theaters].[name], [l_theaters].[created_at], [l_theaters].[updated_at] order by [l_theaters].[id] desc)

[ricksuggs]

@janhartigan @edgji I've experienced the same issue and initially used the workaround of adding each column to the group by clause, however I would like to find a solution that does not require adding functions in a BaseModel as @mnewmedia has shown. I would like to find a solution that only requires modifying the source code of this (FrozenNode/Laravel-Administrator) project.

If the following generated SQL statement is being used to count the number of rows in the table, why is a group by on the primary key added to the statement? Since the primary key is inherently unique, adding a group by for this column should not have any effect.

SELECT COUNT(id) AS aggregate FROM (select [table_name].[id] from [table_name] group by [table_name].[id]) AS agg

Is another possible solution to just remove the group by statement since it appears to only by grouping by a unique primary key? I've been using the following:

https://github.com/FrozenNode/Laravel-Administrator/blob/master/src/Frozennode/Administrator/DataTable/DataTable.php#L116

//get things going by grouping the set
$table = $model->getTable();
$keyName = $model->getKeyName();
//$query = $model->groupBy($table . '.' . $keyName);
$query = $model->newQuery();

//get the Illuminate\Database\Query\Builder instance and set up the count query
$dbQuery = $query->getQuery();
//$countQuery = $dbQuery->getConnection()->table($table)->groupBy($table . '.' .$keyName);
$countQuery = $dbQuery->getConnection()->table($table);

Thanks.

[wenihal]

@ricksuggs: thanks!! That worked for me.