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
-------------------------------------------------------------------------
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
