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 sqlalch...@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.
