Problem With Getting Records When Join Tables To Get Count Of Offers

Yii 1.1 General Discussion
1

I have following code to can sort by offers(bids and buys):




$clean_criteria = new CDbCriteria;

$clean_criteria->order = 'promoted DESC'; 


$criteria = new CDbCriteria;

$criteria->order = 'promoted DESC';

$criteria->select = "*, (COUNT(aa.id) + COUNT(ab.id)) as offers";/

$criteria->join = ' LEFT JOIN auction_bid aa ON (t.id = aa.auction)';

$criteria->join .= ' LEFT JOIN auction_buy ab ON (t.id = ab.auction)';

I am using pagination and sort:


$item_count = Auction::model()->count($clean_criteria);

$pages = new CPagination($item_count);

$pages->setPageSize(5);

$pages->applyLimit($clean_criteria); 

            

$sort = new CSort('Auction');

$sort->attributes = array('offers'=>array('asc'=>'offers', 'desc'=>'offers DESC'));

$sort->applyOrder($clean_criteria);

But to get offers i must use $crtieria when finding auctions:


$auctions = Auction::model()->findAll($criteria);

[s]On site there is correct pages count, but on every there is one same auction.

How to make it work properly?[/s]

EDIT:

This code working properly:


$criteria->select = "*, COUNT(abid.id) AS offers1";

$criteria->join = ' LEFT JOIN auction_bid abid ON (t.id = abid.auction)';

$criteria->group = 't.id';

It returns offers1 = 1 for first auction.

Following too :


$criteria->select = "*, COUNT(abuy.id) AS offers2";

$criteria->join = ' LEFT JOIN auction_buy abuy ON (t.id = abuy.auction)';

$criteria->group = 't.id';

It returns offers2 = 4 for first auction

But when i combine it into one query:


$criteria2->select = "*, COUNT(abid.id) AS offers1, COUNT(abuy.id) AS offers2";

$criteria->join = ' LEFT JOIN auction_bid abid ON (t.id = abid.auction)';

$criteria->join .= ' LEFT JOIN auction_buy abuy ON (t.id = abuy.auction)';

$criteria->group = 't.id';

It returns offers1 = 4 and offers2 = 4 for first auction.

How to fix it?

2

The SQL term with joining both tables in one query is not correct for counting offers1/offers2.

Execute the sql statement in your sql ide and you will see the result.

Use subqueries instead of JOIN:




$criteria->select = "*,

           (SELECT COUNT(id)

              FROM auction_bid 

             WHERE auction = t.id) as offers1,


           (SELECT COUNT(id) 

              FROM auction_buy

             WHERE auction = t.id) as offers2";
3

solved: http://stackoverflow.com/questions/24217916/yiisql-do-not-getting-records-when-join-tables-to-get-count-of-offers