CODEWITHSUNDEEP
phpmysqlkohana
Gavin
Gavin

Reputation: 7944

Kohana DB expression not returning correct results

Can someone please explain how to get the first query to return the same results as the 2nd query? The first query returns 1 instead of 0 for any column with a count of 0. The 2nd query works fine. I'm pretty sure it's a problem with the COUNT() statement.

$lists = DB::select('favorites_lists.*', array(DB::expr('COUNT("favorites.list_id")'), 'total_favs'))
    ->from('favorites_lists')
    ->join('favorites', 'LEFT')
    ->on('favorites_lists.id', '=', 'favorites.list_id')
    ->where('favorites_lists.uid', '=', $user->id)
    ->group_by('favorites_lists.id')
    ->execute()
    ->as_array();

$lists = DB::query(
    Database::SELECT,
    'SELECT favorites_lists.*, COUNT(favorites.list_id) as total_favs
    FROM favorites_lists
    LEFT JOIN favorites
    ON favorites_lists.id = favorites.list_id
    WHERE favorites_lists.uid = ' . $user->id . '
    GROUP BY favorites_lists.id'
)
    ->execute()
    ->as_array();

Upvotes: 1

Views: 296

Answers (1)

Essam Elmasry
Essam Elmasry

Reputation: 1252

DB::select('favorites_lists.*', array(DB::expr('COUNT("favorites.list_id")'), 'total_favs'))

what's the use of this array ? I usually write the query like this .

DB::select('favorites_lists.*',DB::expr('COUNT("favorites.list_id") AS total_favs'))

Upvotes: 2

Related Questions