Something like this:
stmt = Session.query(model.ViewOpt.id_cu, model.ViewOpt.id_meas) \
.filter(model.ViewOpt.id_view==1).subquery()
query = Session.query(model.Sensor) \
.outerjoin((stmt,
and_(model.Sensor.id_cu==stmt.c.id_cu,
model.Sensor.id_meas==stmt.c.id_meas))) \
.filter(and_(stmt.c.id_cu==None, stmt.c.id_meas==None))
Cheers
2011/2/22 neurino <neur...@gmail.com>
> I guess since, I learn it now, EXCEPT is not supported by MySQL...
>
> I guess I'll have to change my query at all...
>
> On Feb 22, 12:57 pm, neurino <neur...@gmail.com> wrote:
> > I have now problems with except_ in MySQL: the code that worked
> flawlessly
> > in sqlite now causes an error, seems right after EXCEPT in query:
> >
> > 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 'SELECT sensors.id_cu AS sensors_id_cu,
> > sensors.id_meas AS sensors_id_meas, senso' at line 3") 'SELECT
> > anon_1.sensors_id_cu AS anon_1_sensors_id_cu, anon_1.sensors_id_meas AS
> > anon_1_sensors_id_meas, anon_1.sensors_id_elab AS anon_1_sensors_id_elab,
> > anon_1.sensors_name AS anon_1_sensors_name, anon_1.sensors_desc AS
> > anon_1_sensors_desc \nFROM (SELECT sensors.id_cu AS sensors_id_cu,
> > sensors.id_meas AS sensors_id_meas, sensors.id_elab AS sensors_id_elab,
> > sensors.name AS sensors_name, sensors.`desc` AS sensors_desc \nFROM
> sensors
> > EXCEPT SELECT sensors.id_cu AS sensors_id_cu, sensors.id_meas AS
> > sensors_id_meas, sensors.id_elab AS sensors_id_elab, sensors.name AS
> > sensors_name, sensors.`desc` AS sensors_desc \nFROM sensors, view_opts
> > \nWHERE sensors.id_cu = view_opts.id_cu AND sensors.id_meas =
> > view_opts.id_meas AND view_opts.id_view = %s AND view_opts.id IS NOT
> NULL
> > ORDER BY sensors.id_cu, sensors.id_meas) AS anon_1 ORDER BY
> > anon_1.sensors_id_cu, anon_1.sensors_id_meas' (1L,)
> >
> > I'm using:
> > * sqlalchemy 0.6.6
> > * MySQLdb 1.2.3
> > * MySQL Ver 14.14 Distrib 5.1.41,
> >
> > Thanks for your support
> >
> > 2011/1/13 neurino <neur...@gmail.com>
> >
> >
> >
> >
> >
> >
> >
> > > Thanks Michael,
> >
> > > just for following readers I precise the ORDER BY clause causing the
> > > OperationalError is the one coming *before* the EXCEPT so I had to
> > > add .order_by(None) to the first query, now it looks like:
> >
> > > Session.query(model.Sensor) \
> > > .order_by(None) \
> > > .except_(
> > > Session.query(model.Sensor) \
> > > .filter(model.Sensor.id_cu==model.ViewOpt.id_cu) \
> > > .filter(model.Sensor.id_meas==model.ViewOpt.id_meas) \
> > > .filter(model.ViewOpt.id_view==1)
> > > )
> >
> > > and works perfectly, thanks again!
> >
> > > Cheers
> > > neurino
> >
> > > On Jan 12, 5:28 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > > > On Jan 12, 2011, at 11:20 AM, neurino wrote:
> >
> > > > > Well as I wrote ordering involves everything, also forms creation
> with
> > > > > formalchemy (make a select where all sensors are ordered that way
> etc)
> > > > > anyway I understand your point of view.
> >
> > > > >> quickest is a "where sensor id not in (query)", as a simple WHERE
> > > clause
> >
> > > > > Problem comes when Sensor primary key is composite (id_cu +
> > > > > id_meas)...
> >
> > > > > The good 'ol python comes in handy anyway:
> >
> > > > >>>> all = Session.query(model.Sensor).all()
> > > > >>>> selected = Session.query(model.Sensor).filter(
> > > > > ... model.Sensor.id_cu==model.ViewOpt.id_cu).filter(
> > > > > ... model.Sensor.id_meas==model.ViewOpt.id_meas).filter(
> > > > > ... model.ViewOpt.id_view==1).all()
> > > > >>>> diff = [sens for sens in all if sens not in selected]
> > > > >>>> len(all), len(selected), len(diff)
> > > > > (154, 6, 148)
> >
> > > > > We're talking of working on max total 200/300 sensors.
> >
> > > > > The OR way did not filter anything (maybe I made somwthing wrong).
> >
> > > > Oh you know what, I completely forgot the best solution. It *is*
> > > documented on query.order_by() though which is an argument
> for....checking!
> > > pass None to query.order_by(). That disables all order_by's for
> that
> > > query. So go back to your except_() and use except_(q.order_by(None)).
> >
> > > > > Greetings
> >
> > > > > On Jan 12, 4:04 pm, Michael Bayer <mike...@zzzcomputing.com>
> wrote:
> > > > >> On Jan 12, 2011, at 8:46 AM, neurino wrote:
> >
> > > > >>> I need always the same order_by in all app and it could be
> subject of
> > > > >>> modification and / or integration in the near future so which
> better
> > > > >>> place than mapper to define it once instead of any time I do a
> query?
> >
> > > > >> It sounds like the ordering here is for the purposes of view logic
> so
> > > I'd have view logic that is factored down to receive Query objects that
> > > return Sensor rows, the view logic then applies the .order_by() to the
> > > Query. I.e. in a web app I use a Paginator object of some kind that
> does
> > > this, given a Query. This is probably a reason I don't like
> "order_by" to
> > > be within mapper(), it doesn't define persistence, rather a view.
> >
> > > > >>> Anyway do you think there are alternate paths to get `all sensors
> but
> > > > >>> already choosen` which are "order_by" compatible?
> >
> > > > >> quickest is a "where sensor id not in (query)", as a simple WHERE
> > > clause, or use OR, "query sensor where sensor.cu != cu OR sensor.meas
> !=
> > > meas OR sensor.view != view". "Except" is not as widely used and I
> think
> > > its not even supported by all backends, even though it is a nice
> logical set
> > > operator, its got annoying quirks like this one.
> >
> > > > >>> Thanks for your support
> >
> > > > >>> On Jan 12, 2:38 pm, Michael Bayer <mike...@zzzcomputing.com>
> wrote:
> > > > >>>> On Jan 12, 2011, at 7:28 AM, neurino wrote:
> >
> > > > >>>>> I have this model:
> >
> > > > >>>>> ``I organize views with many view_options each one showing a
> > > sensor.
> > > > >>>>> A sensor can appear just once per view.``
> >
> > > > >>>>> sensors = Table('sensors', metadata,
> > > > >>>>> Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
> > > > >>>>> primary_key=True,
> > > > >>>>> autoincrement=False),
> > > > >>>>> Column('id_meas', Integer, primary_key=True,
> > > autoincrement=False),
> > > > >>>>> Column('id_elab', Integer, nullable=False),
> > > > >>>>> Column('name', Unicode(40), nullable=False),
> > > > >>>>> Column('desc', Unicode(80), nullable=True),
> > > > >>>>> )
> >
> > > > >>>>> ctrl_units = Table('ctrl_units', metadata,
> > > > >>>>> Column('id', Integer, primary_key=True,
> autoincrement=False),
> > > > >>>>> Column('name', Unicode(40), nullable=False)
> > > > >>>>> )
> >
> > > > >>>>> views = Table('views', metadata,
> > > > >>>>> Column('id', Integer, primary_key=True),
> > > > >>>>> Column('name', Unicode(40), nullable=False),
> > > > >>>>> Column('desc', Unicode(80), nullable=True),
> > > > >>>>> )
> >
> > > > >>>>> view_opts = Table('view_opts', metadata,
> > > > >>>>> Column('id', Integer, primary_key=True),
> > > > >>>>> Column('id_view', Integer, ForeignKey('views.id'),
> > > > >>>>> nullable=False),
> > > > >>>>> Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
> > > > >>>>> nullable=False),
> > > > >>>>> Column('id_meas', Integer, nullable=False),
> > > > >>>>> Column('ord', Integer, nullable=False),
> > > > >>>>> ForeignKeyConstraint(('id_cu', 'id_meas'),
> > > > >>>>> ('sensors.id_cu', 'sensors.id_meas')),
> > > > >>>>> #sensor can appear just once per view
> > > > >>>>> UniqueConstraint('id_view', 'id_cu', 'id_meas'),
> > > > >>>>> )
> >
> > > > >>>>> Now I let the user add view_options letting him select the
> sensor.
> > > > >>>>> I'd like to show him only the sensors not already selected in
> other
> > > > >>>>> options of the same parent view so I tried to use except_ this
> way:
> >
> > > > >>>>> q = Session.query(model.Sensor) \
> > > > >>>>> .except_(
> > > > >>>>> Session.query(model.Sensor) \
> > > > >>>>> .filter(model.Sensor.id_cu==model.ViewOpt.id_cu)
> \
> > > > >>>>>
> .filter(model.Sensor.id_meas==model.ViewOpt.id_meas)
> > > \
> > > > >>>>> .filter(model.ViewOpt.id_view==1)
> > > > >>>>> )
> >
> > > > >>>>> Sensor mapping has a order_by:
> >
> > > > >>>>> orm.mapper(Sensor, sensors,
> > > > >>>>> order_by=[sensors.c.id_cu,
> > > > >>>>> sensors.c.id_meas
> > > > >>>>> ])
> >
> > > > >>>>> I get this SQL and this error, probably due to mapping order_by
> in
> > > > >>>>> Sensor:
> >
> > > > >>>>> (OperationalError) ORDER BY clause should come after EXCEPT not
> > > > >>>>> before
> > > > >>>>> u'SELECT anon_1.sensors_id_cu AS anon_1_sensors_id_cu,
> > > > >>>>> anon_1.sensors_id_meas AS anon_1_sensors_id_meas,
> > > > >>>>> anon_1.sensors_id_elab AS anon_1_sensors_id_elab,
> > > anon_1.sensors_name
> > > > >>>>> AS anon_1_sensors_name, anon_1.sensors_desc AS
> anon_1_sensors_desc
> > > > >>>>> FROM (SELECT sensors.id_cu AS sensors_id_cu, sensors.id_meas AS
> > > > >>>>> sensors_id_meas, sensors.id_elab AS sensors_id_elab,
> sensors.nameAS
> > > > >>>>> sensors_name, sensors."desc" AS sensors_desc
> > > > >>>>> FROM sensors ORDER BY sensors.id_cu, sensors.id_meas EXCEPT
> SELECT
> > > > >>>>> sensors.id_cu AS sensors_id_cu, sensors.id_meas AS
> sensors_id_meas,
> > > > >>>>> sensors.id_elab AS sensors_id_elab, sensors.name AS
> sensors_name,
> > > > >>>>> sensors."desc" AS sensors_desc
> > > > >>>>> FROM sensors, view_opts
> > > > >>>>> WHERE sensors.id_cu = view_opts.id_cu AND sensors.id_meas =
> > > > >>>>> view_opts.id_meas AND view_opts.id_view = ? ORDER BY
> sensors.id_cu,
> > > > >>>>> sensors.id_meas) AS anon_1 ORDER BY anon_1.sensors_id_cu,
> > > > >>>>> anon_1.sensors_id_meas'
> >
> > > > >>>>> is this supposed to be a bug?
> >
> > > > >>>>> Any alternative solution (and maybe simpler :) ) to get what I
> > > need?
> >
> > > > >>>> I suppose its a bug, though I'm not a huge fan of "order_by" on
> > > mapper though, so my recommendation would be to not rely upon that. A
> > > solution in SQLA would be if some flag were passed through to not
> render
> > > built-in order bys. I've added 2022 targeted for 0.7.xx for that.
> >
> > > > >>> --
> > > > >>> You received this message because you are subscribed to the
> Google
> > > Groups "sqlalchemy" group.
> > > > >>> To post to this group, send email to sqlalchemy@googlegroups.com
> .
> > > > >>> To unsubscribe from this group, send email to
> > > sqlalchemy+unsubscr...@googlegroups.com.
> > > > >>> For more options, visit this group athttp://
> > > groups.google.com/group/sqlalchemy?hl=en.
> >
> > > > > --
> > > > > You received this message because you are subscribed to the Google
> > > Groups "sqlalchemy" group.
> > > > > To post to this group, send email to sqlalchemy@googlegroups.com.
> > > > > To unsubscribe from this group, send email to
> > > sqlalchemy+unsubscr...@googlegroups.com.
> > > > > For more options, visit this group athttp://
> > > groups.google.com/group/sqlalchemy?hl=en.
> >
> > > --
> > > You received this message because you
> >
> > ...
> >
> > read more ยป
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
