Open Stack

Hi-

While developing the new extension, I was struck at the SQLalchemy ORM
query,

To achieve, the query below in ORM,

select distinct(inst.hostname) as server_name,
       fip.address as fixed_ip_address,
   vif.address as fixed_ip_mac_address
from instances inst, instance_metadata mtd, virtual_interfaces vif,
fixed_ips fip
where  inst.id = mtd.instance_id      and
       mtd.instance_id = vif.instance_id  and
       vif.instance_id = fip.instance_id  and
   inst.project_id = 'e216fcb54dc944a8ab16e4e325299643' and
   mtd.key = 'Server Group' and
   mtd.value = 'DOM1'
group by mtd.key,mtd.value;

SQL>
+-------------+------------------+----------------------+
| server_name | fixed_ip_address | fixed_ip_mac_address |
+-------------+------------------+----------------------+
| serverpoc   | 172.15.1.2       | fa:16:3e:56:47:71    |
| serverpoc2  | 172.15.1.3       | fa:16:3e:4f:3c:9b    |
+-------------+------------------+----------------------+


I have written the ORM query as

result =
session.query(models.Instance.hostname.distinct(),models.FixedIp.address,models.VirtualInterface.address).\
                    join((models.InstanceMetadata,
                          models.InstanceMetadata.instance_id ==
models.Instance.id)).\
                    join ((models.FixedIp,
                          models.FixedIp.instance_id ==
models.InstanceMetadata.instance_id)).\
                    join ((models.VirtualInterface,
                           models.VirtualInterface.instance_id ==
models.FixedIp.instance_id)).\
                    filter(and_(models.Instance.project_id ==
search_opts['project_id'])).\
                    filter(and_(models.InstanceMetadata.key ==
str(search_opts['key']) )).\
                    filter(and_(models.InstanceMetadata.value ==
str(search_opts['value']))).\
                    all()

I'm new to sqlalchemy and with the help from existing queries in nova api,
I have come upto this level.

Can any one guide me why the above ORM query is not returning rows? Is the
ORM query reflecting correctly the SQL query above.

Please guide me troubleshoot the same. I was unable to plot the mistake in
the ORM. Am i correct with respect to the ORM query preparation?


Thanks a lot in advance.

-- 
Regards,
----------------------------------------------
Trinath Somanchi,
+91 9866 235 130
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20121003/8d8e2ac6/attachment.html>