On Wed, Oct 3, 2012 at 5:02 PM, Trinath Somanchi
<trinath.soman
...@gmail.com> wrote:
> Hi-
> I have a sql query which is returning 2 rows. But when is transformed to ORM
> query, its not returning any rows.
> My SQL Statement:
> 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,mo dels.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()
> Can any one help me find the fault in the ORM query.
> Thanks in advance.
Have you tried turning on SQL logging (eg. by passing echo=True to
create_engine), and comparing the query with your original? If the
query looks ok, maybe the parameters you are passing aren't exactly
what you think they should be.
(Also, I'm not sure if it makes any difference, but those "and_()"
calls inside filter() are unnecessary - filtering a query already
implies that you are AND-ing the condition with all the previous
conditions)
Simon
