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'm using SqlAlchemy 0.6.4
--
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.
