Fuelphp SUM in join issue

Question

I'm having a bit of a problem with join SUM in fuel php.

When I use it like this

$query = DB::select(
            'stream_post.*',
            'SUM(stream_comment.comment_stream_id)'
            )->from('stream_post');
        $query->join('stream_comment', 'LEFT');
        $query->on('stream_post.stream_id', '=', 'stream_comment.comment_stream_id');
        $query->join('users_metadata');
        $query->on('stream_post.user_id', '=', 'users_metadata.user_id');
        $query->limit(10);
        $query->order_by('stream_id', 'DESC');
        $result = $query->execute();
        if(count($result) > 0) {    
            foreach($result as $row)
            {
                $data[] = $row;
            }

            return $data;
        }

I get this error

Column not found: 1054 Unknown column 'SUM(stream_comment.comment_stream_id)' in 'field What do im doing wrong?

Answer 1

You need to use the expr function to create an expression in the select statement

$result = DB::select(DB::expr(' SUM(stream_comment.comment_stream_id) as count'))->from('stream_post')->execute();

Documented here http://docs.fuelphp.com/classes/database/usage.html