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?
Anyway do you think there are alternate paths to get `all sensors but already choosen` which are "order_by" compatible? Thanks for your support On Jan 12, 2:38 pm, Michael Bayer <[email protected]> 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 [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.
