Relational AR does not work properly with CDbCriteria

Today i was wondering why a query, build by CDbCriteria and executed with findAll(), failed without changing the query at all.

This is how i build the query:


[…] // This is executed after $form is submitted

$model = Machine::model()->with(‘realms’, ‘service’, ‘ips’);


$model->dbCriteria->addSearchCondition('realms.fqdn', $form->model->fqdn);


$model->dbCriteria->addSearchCondition('sysid', $form->model->sysid);


$model->dbCriteria->addSearchCondition('service.service', $form->model->service);


$model->dbCriteria->addSearchCondition('INET_NTOA(ips.ip2)', $form->model->ip);

// This line causes problems with findAll();

$model->dbCriteria->limit = 5;[/PHP]

I do not know if this is a relevant information but:

‘realms’ is a “HAS_MANY” relation to Machine

‘service’ is a “BELONG_TO” relation to Machine

‘ips’ is a “HAS_MANY” relation to Machine

As stated in the code, the query fail to execute when calling $model->findAll() but works well when the line is not added (But only if a search condition for the HAS_MANY relations is added. e.g.: if searching for a service the query works!)

This is the statement which fails (because limit=5):

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ips.ip2' in 'where clause'. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`sysid` AS `t0_c1`, `t`.`service_id` AS `t0_c2`, `service`.`id` AS `t2_c0`, `service`.`service` AS `t2_c1` FROM `Machine` `t` LEFT OUTER JOIN `Service` `service` ON (`t`.`service_id`=`service`.`id`) WHERE (INET_NTOA(ips.ip2) LIKE :ycp0) LIMIT 5

When i delete the statement, the following (working) query is executed (copied from CWebLogRoute output) [$form->model->ip = 10, everything else is empty]

[CODE]Querying SQL: SELECT AS t0_c0, t.sysid AS t0_c1,

t.service_id AS t0_c2, AS t1_c0, realms.origin_id

AS t1_c1, realms.machine_id AS t1_c2, AS t1_c3,

realms.domain AS t1_c4, realms.fqdn AS t1_c5, AS

t2_c0, service.service AS t2_c1, AS t3_c0,

ips.origin_id AS t3_c1, ips.machine_id AS t3_c2, ips.ip1 AS

t3_c3, ips.ip2 AS t3_c4 FROM Machine t LEFT OUTER JOIN Realm

realms ON ( LEFT OUTER JOIN Service

service ON ( LEFT OUTER JOIN IP ips

ON ( WHERE (INET_NTOA(ips.ip2) LIKE :ycp0)[/CODE]

Any ideas on that?

try adding a together() before your findAll()