How to count_all() in Kohana 3.3 with Mysqli drivers

Question

I am trying to use the count_all() method in kohana 3.3 to count all the rows of a table where the id equals a user_id. Here is my controller

public function action_get_messages()
        {
            $user_id = $this->request->param('id');
            $messages = new Model_Message;
            if ($user_id)
            {
                $messages = $messages->where('user_id', '=', $user_id);
                $messages->reset(FALSE);
                $message_count = $messages->count_all();
            }
                else
                {
                    $message_count = $messages->count_all();
                }
            $pagination = Pagination::factory(array(
                'total_items' => $message_count,
                'items_per_page' => 3,
            ));
            $pager_links = $pagination->render();
            $messages = $messages->get_all($pagination->items_per_page, $pagination->offset, $user_id);
            $this->template->content = View::factory('profile/messages')
                ->set('messages', $messages)
                ->set('pager_links', $pager_links);
        }

But when i run the code i get these error message:

"Database_Exception [ 1054 ]: Unknown column 'COUNT("*")' in 'field list' [ SELECT COUNT("*") AS records_found FROM messages AS message WHERE user_id = '2' ]"

What does this error mean and where is the error in my code? Thanks in advance!

Answer 1

The error is on line 1054, which I would guess is the count_all() call.

I'm confused as to why you are calling a reset() on your object. This may be causing issues. You are also casting the object to itself, which is unnecessary. That section of code could look like this:

if ($user_id)
{
    $messages->where('user_id', '=', $user_id);
    $message_count = $messages->count_all();
}

I would advise using the ORM::factory to build your new models in the first place.

The Kohana documentation isn't great, but I would advise reading and looking through the ORM User Guide as ORM will save you a hell of a lot of time in the long run, especially if it's a large project.