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