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