On Wed, Oct 3, 2012 at 5:02 PM, Trinath Somanchi <[email protected]> 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,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() > > 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 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
