I wrote in my first post, that I am expecting result in SQL - close to this one "select e.id, e.type_id, tv.version as min_version, tv2.version as max_version from engines e join versions tv on(e.min_version_id=tv.id) join versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between tv.version and tv2.version;". I don't know how to call tables to check condition in between clause.
On 22 Maj, 22:30, "Michael Bayer" <[email protected]> wrote: > that means min_version and max_version aren't columns. I guess you're > looking for Version.version, in which case you probably need to JOIN to > that table twice on both the "min_version" and "max_version". write (and > test) the query you want in SQL first to get an idea for what you're > doing. > > sniipe wrote: > > > After change (delete 'version' attribute): > > > ProgrammingError: (ProgrammingError) (1064, "You have an error in your > > SQL syntax; check the manual that corresponds to your MySQL server > > version for the right syntax to use near 'AND' at line 3") u'SELECT > > engines.id AS engines_id, engines.min_version_id AS > > engines_min_version_id, engines.max_version_id AS > > engines_max_version_id, engines.type_id AS engines_type_id \nFROM > > engines LEFT OUTER JOIN versions ON engines.min_version_id = > > versions.id \nWHERE %s BETWEEN AND ' ['7.0.1.4'] > > > On 22 Maj, 21:40, "Michael Bayer" <[email protected]> wrote: > >> Engine.min_version and max_version are instrumented column attributes. > >> they don't have an attribute called "version". i think you want > >> between(x, Engine.min_version, Engine.max_version). > > >> sniipe wrote: > > >> > Ok It's working but I can't use 'between': > > >> > engine = meta.Session.query(Engine).outerjoin((Version, > >> > Engine.min_version_id==Version.id)).filter(between(request.POST > >> > ['version'], Engine.min_version.version, > >> > Engine.max_version.version)).all() > > >> > and I've got error: > > >> > AttributeError: Neither 'InstrumentedAttribute' object nor > >> > 'Comparator' object has an attribute 'version' > > >> > What am I doing wrong? > > >> > On 22 Maj, 17:59, "Michael Bayer" <[email protected]> wrote: > >> >> sniipe wrote: > > >> >> > Hi :) > > >> >> > I have three tables: > > >> >> > 1) > >> >> > t_version = sa.Table("versions", meta.metadata, > >> >> > sa.Column("id", sa.types.Integer(), primary_key=True, > >> >> > autoincrement=True), > >> >> > sa.Column("version", mysql.MSChar(length=100, > >> >> > collation='utf8_polish_ci'), nullable=False, unique=True) > >> >> > ) > > >> >> > class Version(object): > >> >> > pass > > >> >> > orm.mapper(Version, t_version) > > >> >> > 2) > >> >> > t_type = sa.Table("types", > >> >> > meta.metadata, > >> >> > sa.Column("id", sa.types.Integer(), primary_key=True, > >> >> > autoincrement=True), > >> >> > sa.Column("name", mysql.MSChar(length=100, > >> >> > collation='utf8_polish_ci'), nullable=False, unique=True), > >> >> > ) > > >> >> > class Type(object): > >> >> > pass > > >> >> > orm.mapper(Type, t_type, properties = { > >> >> > 'engine' : orm.relation(Engine, uselist=False, > >> >> > backref='Type_Engine') > >> >> > }) > > >> >> > 3) > >> >> > t_engine = sa.Table("engines", meta.metadata, > >> >> > sa.Column("id", sa.types.Integer(), primary_key=True, > >> >> > autoincrement=True), > >> >> > sa.Column("min_version_id", sa.types.Integer(), sa.ForeignKey > >> >> > ("versions.id"), nullable=False), > >> >> > sa.Column("max_version_id", sa.types.Integer(), sa.ForeignKey > >> >> > ("versions.id"), nullable=False), > >> >> > sa.Column("type_id", sa.types.Integer(), sa.ForeignKey > >> >> > ("types.id"), nullable=False), > >> >> > ) > > >> >> > class Engine(object): > >> >> > pass > > >> >> > orm.mapper(Engine, t_engine, properties = { > >> >> > 'type' : orm.relation(Type, uselist=False, > >> backref='Engine_Type'), > >> >> > 'min_version' : orm.relation(Version, > >> >> > primaryjoin=t_engine.c.min_version_id==t_version.c.id), > >> >> > 'max_version' : orm.relation(Version, > >> >> > primaryjoin=t_engine.c.max_version_id==t_version.c.id) > >> >> > }) > > >> >> > My problem is how to make query equal this SQL instruction "select > >> >> > e.id, e.type_id, tv.version as min_version, tv2.version as > >> max_version > >> >> > from engines e join versions tv on(e.min_version_id=tv.id) join > >> >> > versions tv2 on(e.max_version_id=tv2.id) where '7.0.1.32' between > >> >> > tv.version and tv2.version;" > > >> >> > I've tried to do something like that: > > >> >> > engine = > >> meta.Session.query(Engine).outerjoin(Version).filter(between > >> >> > (request.POST['version'], Engine.min_version.version, > >> >> > Engine.max_version.version)).all() > > >> >> when you do the outerjoin, pass it the relation you're joining on or > >> an > >> >> onclause, such as > > >> >> query.outerjoin(Version.engine) > > >> >> or > > >> >> query.outerjoin((Version, Engine.some_col==Version.some_other_col)) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
