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:
[PHP]
[…] // This is executed after $form is submitted
$model = Machine::model()->with(‘realms’, ‘service’, ‘ips’);
if(!empty($form->model->fqdn))
$model->dbCriteria->addSearchCondition('realms.fqdn', $form->model->fqdn);
if(!empty($form->model->sysid))
$model->dbCriteria->addSearchCondition('sysid', $form->model->sysid);
if(!empty($form->model->service))
$model->dbCriteria->addSearchCondition('service.service', $form->model->service);
if(!empty($form->model->ip))
$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 5When 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 t.id AS t0_c0, t.sysid AS t0_c1,
t.service_id AS t0_c2, realms.id AS t1_c0, realms.origin_id
AS t1_c1, realms.machine_id AS t1_c2, realms.name AS t1_c3,
realms.domain AS t1_c4, realms.fqdn AS t1_c5, service.id AS
t2_c0, service.service AS t2_c1, ips.id 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 (realms.machine_id=t.id) LEFT OUTER JOIN Service
service ON (t.service_id=service.id) LEFT OUTER JOIN IP ips
ON (ips.machine_id=t.id) WHERE (INET_NTOA(ips.ip2) LIKE :ycp0)[/CODE]
Any ideas on that?