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.name AS
> >>>>> 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 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.
