CI db->select() and COUNT

[eluser]Bikun[/eluser]
Hi All!

I'm trying to use COUNT inside db->select(). Here is a code I have:

Code:

$this->db->from('orders');
$this->db->select('COUNT(bids.id) AS offers_count, orders.id AS order_id, orders.title, auctions.id AS auction_id, auctions.price');
$this->db->join('auctions', 'orders.id = auctions.order_id', 'right');
$this->db->join('bids', 'auctions.id = bids.auction_id', 'left');
$this->db->where('user_id', '1');
$this->db->where('orders.status', $this->WAIT_OFFERS);
$this->db->orderby('orders.id', 'desc');

$data['query'] = $this->db->get();

and here is an error I get:

Code:

Error Number: 1054

Unknown column 'COUNT(bids.id)' in 'field list'

SELECT `COUNT(bids`.`id)` AS offers_count, `orders`.`id` AS order_id, `orders`.`title`, `auctions`.`id` AS auction_id, `auctions`.`price` FROM (`orders`) RIGHT JOIN `auctions` ON `orders`.`id` = `auctions`.`order_id` LEFT JOIN `bids` ON `auctions`.`id` = `bids`.`auction_id` WHERE `user_id` = '1' AND `orders`.`status` = 2 ORDER BY `orders`.`id` desc

As you can see, CI thinks that `COUNT(bids`.`id)` is a name of the column. How can I make to think him another way?

Thanks!

[eluser]ray73864[/eluser]
COUNT() is a group function in MySQL, so even if you got the current column error sorted out you would still receive an error from the database about mixing a group function with non-group stuff.

Why not use the count_all_results() active record function after running the query?

[eluser]davidbehler[/eluser]
If you want to run that query you can this to make it work:
Add a second parameter, FALSE, to your call of the select function

Code:

$this->db->select('COUNT(bids.id) AS offers_count, orders.id AS order_id, orders.title, auctions.id AS auction_id, auctions.price', FALSE);

That will prevent CI from using protecting/escaping your field names with backticks.

Add a call to the group_by with the same parameters as for the select function except the field you use the group function on and the second parameter

Code:

$this->db->group_by("orders.id AS order_id, orders.title, auctions.id AS auction_id, auctions.price");

See use guide for further info.

[eluser]Bikun[/eluser]
Thanks guys. The way waldmeister offered works perfectly.