1054 Unknown column 'total' in 'order clause'' ?

[moorer2k]

I am pretty new to laravel so forgive me if this all is just silly to ask.. I am using mysql database. The site template and such looks great! Able to see all of the info except events and errors (they are in teh database, however). A lot of errors like this when I try to view the pages.

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total' in 'order clause' (SQL: select count(*) as aggregate from (select '1' as row from tracker_events inner join tracker_events_log on tracker_events_log.event_id = tracker_events.id where tracker_events_log.updated_at >= 2014-12-26 23:38:41 and tracker_events_log.updated_at <= 2014-12-27 23:38:41 group by tracker_events.id, tracker_events.name order by total desc) AS count_row_table)' in /laravel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:625

I am getting this. I am unsure why it's claiming to be missing the "total" column. I re-built the migration tables and it all works fine, except that. Any clues?

[amadeomano]

I have exactly the same problem, unfortunately i coudn't find a soloution for that, but it seems that the problem belongs to blim\Datatables package. it's probably possible to reimplement that part using another package like chumper\Datatable.

[antonioribeiro]

How would you do this in MySQL? :)

$query =
        $this
            ->select(
                'tracker_events.id',
                'tracker_events.name',
                $this->getConnection()->raw('count(tracker_events_log.id) as total')
            )
            ->from('tracker_events')
            ->period($minutes, 'tracker_events_log')
            ->join('tracker_events_log', 'tracker_events_log.event_id', '=', 'tracker_events.id')
            ->groupBy('tracker_events.id', 'tracker_events.name')
            ->orderBy('total', 'desc');

The problem is, probably, that MySQL is not accepting to order by this alias:

$this->getConnection()->raw('count(tracker_events_log.id) as total')

[antonioribeiro]

Not having any problems with MySQL 5.6.19-0ubuntu0.14.04.1 in the last version of Tracker. Closing it for now.