(Apologies for the somewhat long-winded subject line; should cover the whole question though.)
Hi folks, I'm new to SQLAlchemy, and rather new to SQL in general, so I've been stumbling along trying to get a Pylons project of the ground (using the SQLAlchemy setup as outlined at http://www.rexx.com/~dkuhlman/pylons_quick_site.html). The thing I've now come across, and to which I cannot find a fully satisfying solution: how can I create multiple filter criteria using relations, preferably step by step (ie, depending on certain conditions/if blocks)? An example hopefully clarifies the question. Say I have my example database containing 3 tables. The 'computer' table has id, name and price columns, as well as 2 foreign keys (disk_id & cpu_id), pointing to 'disk' and 'cpu' tables. 'disk' has id and size columns, and 'cpu' has id and speed columns. My mapping is set up as follows: <code> computers = Table('computer', metadata, autoload=True) disks = Table('disk', metadata, autoload=True) cpus = Table('cpu', metadata, autoload=True) class Computer(object): pass class Disk(object): pass class Cpu(object): pass mapper(Disk, disks) mapper(Cpu, cpus) mapper(Computer, computers, properties=dict( disk=relation(Disk, backref='computer'), cpu=relation(Cpu, backref='cpu'))) </code> So I can now do the following, filtering on columns in the relations: <code> comps = session.query(Computer) if True: # or some other condition comps = comps.filter(and_(Disk.c.size>100, Computer.c.disk_id==Disk.c.id)) if True: # or any condition comps = comps.filter(and_(Cpu.c.speed>2, Computer.c.cpu_id==Cpu.c.id)) # Sorting for nicely displaying comps = comps.order_by(Disk.c.size).order_by(Computer.c.price) for c in comps: print c.price, c.name, c.cpu.speed, c.disk.size print </code> But using the ids explicitly seems to defy the relations defined with the mapper somewhat. I would like to be able to do (showing only the filter() statements): <code> comps = comps.filter(Disk.c.size>100) comps = comps.filter(Cpu.c.speed>2) </code> but for that I apparently need to explicitly define a join(), which seems to work only when using one relation: <code> comps = comps.join('disk') comps = comps.filter(Disk.c.size>100) </code> The above works, but adding the following to the above results in an SQL error, because the 'computer' table is joined twice to the SQL statement: <code> comps = comps.join('cpu') comps = comps.filter(Cpu.c.speed>100) </code> filter_by() would do the job, but I can only use that with equal statements [comps = session.query(Computer).filter_by(size=100).comps.filter_by(speed=2.0)] (not that I would actually compare floats with an equal sign anyway). Any suggestions or pointers to what I might have missed in the docs? Or is this (yet) simply not possible? Thanks, Evert Python version 2.4.4; SQLAlchemy version 0.3.6; Pylons version0.9.4.1; Postgres 8.2.3 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
