Joins Not Created Correctly

Yii 1.1 General Discussion
1

Hi,

I have a Booking model, where each Booking HAS_MANY transfers.

I’m using CDBcriteria to do a search, which I also need the total number of rows:




        $count = Booking::model()->with('transfers')->count($criteria);

        $pages = new CPagination($count);

        $pages->pageSize = 30;

        $pages->applyLimit($criteria);

        return array(

            'bookings' => Booking::model()->with('transfers')->findAll($criteria),

            'pages' => $pages

        );

As you can see, there are two queries involved, the first one to get the count(*) and the second one that fetches the records, applying the limit and offset for pagination.

The problem is that it looks like Yii caches the joins, because the join is only being done in the first SQL, and the second SQL doesn’t contain the join expression, which breaks the SQL statement, because I’m using a search condition based on the joined table:

First SQL:


system.db.CDbCommand.query(SELECT COUNT(DISTINCT `t`.`id`) FROM `bookings` `t` LEFT OUTER JOIN `transfers` `transfers` ON (`transfers`.`booking_id`=`t`.`id`) WHERE ((date(created_at) >= '2012-10-03') AND (transfers.lead_surname LIKE :ycp0)))

Second SQL:




system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `t`.`customer_id` AS `t0_c1`, `t`.`created_at` AS `t0_c2`, `t`.`ref` AS `t0_c3`, `t`.`ip` AS `t0_c4`, `t`.`ip_info` AS `t0_c5`, `t`.`price_cancellation` AS `t0_c6`, `t`.`price_discount` AS `t0_c7`, `t`.`discount_code` AS `t0_c8`, `t`.`payment_state` AS `t0_c9`, `t`.`email_customer` AS `t0_c10`, `t`.`email_supplier` AS `t0_c11`, `t`.`mark` AS `t0_c12`, `t`.`supplier_fact` AS `t0_c13` FROM `bookings` `t` WHERE ((date(created_at) >= '2012-10-03') AND (transfers.lead_surname LIKE :ycp0)) LIMIT 30)

The second SQL gives error, because the transfers table is not being joined… :( Any idea why? Is there any workaround?

Thanks

2

Try it with


$criteria->together = true;
3

Thanks Keith, that worked like a charm!