So I have this simple query that if I run it on sql server management studio it gives me results.
Select name, count(*) As thecount from Restaurants group by name
I am using codeigniter and a wrapper library for datatables called ignited datatable . I am using it because its easier to integrate sql results to a nice json format for tables. The problem I ran into is that the group by function used by this library gives me an error.. This is my query
$this->datatables->select("name, count(*) as thecount");
$this->datatables->from("Restaurants");
$this->datatables->group_by('name');
echo $this->datatables->generate();
I believe this is a straightforward query it shouldn't give me any errors but when it echoes it gives me a database error
Error Number: 42000
[Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'Restaurants.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT * FROM Restaurants GROUP BY name (<- Note: Why is trying to select all columns?)
There is something wrong with the group_by functionality in this library because if I do another query without the group by it works just fine.
This is the select and group_by functionality in the datatables library
public function select($columns, $backtick_protect = TRUE)
{
foreach($this->explode(',', $columns) as $val)
{
$column = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val));
$column = preg_replace('/.*\.(.*)/i', '$1', $column); // get name after `.`
$this->columns[] = $column;
$this->select[$column] = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$1', $val));
}
$this->ci->db->select($columns, $backtick_protect);
return $this;
}
public function group_by($val)
{
$this->group_by[] = $val;
$this->ci->db->group_by($val);
return $this;
}
I would also like to mention if i accidently mispell something like this $this->datatables->group_by('nam');
I would get
a different error
Error Number: 42S22
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'name'.
SELECT AccountName, count(*) AS totalcount FROM ftmAccounts GROUP BY [name]
The point is selecting the right columns when i mispell something