Thanks for the quick response. I added query.join_to('street_name') to
the query.select and that seems to be working (and is much nicer than
the get-street_name_ids-first method).One thing I don't understand is why I don't need a query.join_to for the nodes, cities, and states tables too, since they are related to the streets table similarly to the way street_names is. The only difference I see is that I specified primaryjoin for the other tables but not for street_names, but when I tried adding primaryjoin for street_names, it didn't (seem to) do anything. I don't really need an answer to that question, mostly just curious. Thanks again, -- Wyatt Baldwin byCycle.org On 9/18/06, Michael Bayer <[EMAIL PROTECTED]> wrote: > OK, youre doing pretty good. theres one behavior of Query which is > less than convenient for a case like this, which is that when you use > multiple tables in the <whereclause> of a session.query > (SomeClass).select(<whereclause>) , namely tables that are outside > the scope of the primary table of the mapper which you are querying, > it will not make any assumptions whatsoever about the join conditions > to those other tables, *even* if those other tables are part of > relationships to that mapper. > > so you can either explicitly specify all the join conditions in your > call to select(), or you can get some help in generating them by > using any of the following tools: > > query.select_by() > query.join_by() > query.join_via() > > which *do* make usage of the relations off the mapper, and which are > described at: http://www.sqlalchemy.org/docs/ > datamapping.myt#datamapping_selectrelations_jointo > > > > On Sep 18, 2006, at 9:03 PM, Wyatt Baldwin wrote: > > > Preface: I'm converting an app from MySQL/MySQLdb to > > PostgreSQL/psycopg2/SQLAlchemy. My general SQL experience is limited, > > and this is my first time using SQLAlchemy. So far, the conversion > > process has been mostly smooth and I've been able to clean up a lot of > > things. > > > > > > I've just started getting into the ORM aspects of SQLAlchemy and I'm > > trying to do something fairly complicated (I guess): > > > > - The user enters a free-form street address, which may be only partly > > specified (that is, certain parts, like city or zip code might be > > missing). > > > > - The app looks for all streets (AKA edges) in the database that match > > what the user entered and returns a list of street objects. Here's the > > query I'm trying to use to accomplish this: > > > > edges = query.select(and_( > > func.least(streets.c.addr_f, streets.c.addr_t) <= num, > > num <= func.greatest(streets.c.addr_f, streets.c.addr_t), > > street_names.c.prefix == street_name.prefix, > > street_names.c.name == street_name.name, > > street_names.c.sttype == street_name.sttype, > > street_names.c.suffix == street_name.suffix > > cities.c.city == place.city, > > states.c.id == place.state_id, > > or_(c.zip_code_l == place.zip_code, c.zip_code_r == > > place.zip_code), > > )) > > > > The problem is, in cases where there should be only one result, there > > are, for example, 38. The part that seems to be borked is the > > street_names stuff (something to do with the way the tables are > > joined???). > > > > The streets table has a foreign key pointer, street_name_id, to the > > street names table (see table defs below). If I get the > > street_name_ids in a separate query and do the following, I get the > > expected number of results: > > > > edges = query.select(and_( > > func.least(streets.c.addr_f, streets.c.addr_t) <= num, > > num <= func.greatest(streets.c.addr_f, streets.c.addr_t), > > streets.c.street_name_id.in_(*street_name_ids), > > cities.c.city == place.city, > > states.c.id == place.state_id, > > or_(c.zip_code_l == place.zip_code, c.zip_code_r == > > place.zip_code), > > )) > > > > > > Here's how things are set up (with some stuff left out for clarity): > > > > Tables > > =================== > > > > streets = Table( > > 'layer_streets', > > metadata, > > Column('id', Integer, primary_key=True), > > Column('geom', Geometry, nullable=False), > > > > Column('node_f_id', Integer, > > ForeignKey('layer_nodes.id'), > > nullable=False), > > Column('node_t_id', Integer, > > ForeignKey('layer_nodes.id'), > > nullable=False), > > > > Column('addr_f', Integer), > > Column('addr_t', Integer), > > > > Column('even_side', CHAR(1)), > > > > Column('street_name_id', Integer, ForeignKey > > ('street_names.id'),), > > > > Column('city_l_id', Integer, ForeignKey('cities.id')), > > Column('city_r_id', Integer, ForeignKey('cities.id')), > > > > Column('state_l_id', CHAR(2), ForeignKey('states.id'), > > nullable=False), > > Column('state_r_id', CHAR(2), ForeignKey('states.id'), > > nullable=False), > > > > Column('zip_code_l', Integer), > > Column('zip_code_r', Integer), > > > > schema=schema, > > ), > > > > street_names = Table( > > 'street_names', > > metadata, > > Column('id', Integer, primary_key=True), > > Column('prefix', String(2)), > > Column('name', String, nullable=False), > > Column('sttype', String(4)), > > Column('suffix', String(2)), > > schema=schema, > > ), > > > > > > Objects > > =================== > > > > class Street: > > """Things and stuff which is not relevant to this discussion.""" > > > > class StreetName: > > """Things and stuff which is not relevant to this discussion.""" > > > > > > Mappers > > =================== > > > > street_name_mapper = orm.mapper( > > StreetName, > > street_names, > > ) > > > > edge_mapper = orm.mapper( > > Street, > > layer_streets, > > properties={ > > 'node_f': orm.relation( > > Node, > > primaryjoin=layer_streets.c.node_f_id == > > layer_nodes.c.id, > > ), > > 'node_t': orm.relation( > > Node, > > primaryjoin=layer_streets.c.node_t_id == > > layer_nodes.c.id, > > ), > > 'street_name': orm.relation( > > StreetName, > > ), > > 'city_l': orm.relation( > > City, > > primaryjoin=layer_streets.c.city_l_id == > > cities.c.id, > > ), > > 'city_r': orm.relation( > > City, > > primaryjoin=layer_streets.c.city_r_id == > > cities.c.id, > > ), > > 'state_l': orm.relation( > > State, > > primaryjoin=layer_streets.c.state_l_id == > > states.c.id, > > ), > > 'state_r': orm.relation( > > State, > > primaryjoin=layer_streets.c.state_r_id == > > states.c.id, > > ), > > } > > ) > > > > > > So, the question is, then, of course, what am I doing wrong? Is it the > > query? The mapper? Should I define another mapper for this purpose? > > Would this be a lot easier if I just knew more about SQL in general? > > > > Any tips, pointers, or hints will be most appreciated. > > > > -- > > Wyatt Baldwin > > byCycle.org > > > > ---------------------------------------------------------------------- > > --- > > Take Surveys. Earn Cash. Influence the Future of IT > > Join SourceForge.net's Techsay panel and you'll get the chance to > > share your > > opinions on IT & business topics through brief surveys -- and earn > > cash > > http://www.techsay.com/default.php? > > page=join.php&p=sourceforge&CID=DEVDEV > > _______________________________________________ > > Sqlalchemy-users mailing list > > Sqlalchemy-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users > > -- Wyatt Baldwin byCycle.org ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
