Finding the latest matching object from a relationship?

[Chris Withers]

Hi All,

With the model from my previous post, I'm looking to find the latest
Record for a particular Unit.

Someone on #sqlalchemy suggested this:

unit = session.query(Unit).filter(Unit.id==3).first()

record = unit.records.order_by(Record.date.desc()).first()

...however, this gives me:

AttributeError: 'InstrumentedList' object has no attribute 'order_by'

How should I be looking to do this?

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

[Chris Withers]

Chris Withers wrote:
> record = unit.records.order_by(Record.date.desc()).first()

oops, I meant:

record = unit.records.order_by(Record.timestamp.desc()).first()

...however, this still gives me:

[a...@svilendobrev.com]

use the relation as a join path, and then whatever
filtering/ordering....

try:
unit.join(Unit.records).order_by(Record.date.desc()).first()
or
unit.join('records').order_by(Record.date.desc()).first()

[Chris Withers]

That gives me:

AttributeError: 'Unit' object has no attribute 'join'

[a...@svilendobrev.com]

ah, it has to be other way around
unit = session.query(Unit).filter(Unit.id==3).first()
rec = sess.query( Record).filter( Record.theunit == unit)...
where theuint is the backref of Unit.records.

see
http://www.sqlalchemy.org/docs/05/ormtutorial.html#common-relation-operators

or just wait for Mike (-:)

ciao
svil
svilendobrev.com
http://www.linkedin.com/in/svilendobrev

[Chris Withers]

a...@svilendobrev.com wrote:
> rec = sess.query( Record).filter( Record.theunit == unit)...

Is this as "nice" as I can get it or is their a slicker solution?

> or just wait for Mike (-:)

Hopefully Mr Bayer can enlighten me :-)

[a...@svilendobrev.com]

On Friday 13 February 2009 11:02:13 Chris Withers wrote:
> a...@svilendobrev.com wrote:
> > rec = sess.query( Record).filter( Record.theunit == unit)...
> Is this as "nice" as I can get it or is their a slicker solution?

this might work too
rec = sess.query( Record).filter_by( theunit = unit)...

[Michael Bayer]

On Feb 12, 2009, at 6:42 PM, Chris Withers wrote:

>
> Hi All,
>
> With the model from my previous post, I'm looking to find the latest
> Record for a particular Unit.
>
> Someone on #sqlalchemy suggested this:
>
> unit = session.query(Unit).filter(Unit.id==3).first()
>
> record = unit.records.order_by(Record.date.desc()).first()
>
> ...however, this gives me:
>
> AttributeError: 'InstrumentedList' object has no attribute 'order_by'
>
> How should I be looking to do this?

the most straightforward, assuming you'd like to keep this in SQL,
would be:

session
.query(Record).with_parent(unit).order_by(Record.date.desc()).first()

alternatively, to do exactly what you stated above, i.e.
unit.records.order_by()... etc., you can use a "dynamic" relation
which establishes relations as Query objects. use lazy="dynamic" for
this and see the examples in the "Large Collections" portion of the
documentation.

[Chris Withers]

Michael Bayer wrote:
>
> session
> .query(Record).with_parent(unit).order_by(Record.date.desc()).first()

OK, this works...

> alternatively, to do exactly what you stated above, i.e.
> unit.records.order_by()... etc., you can use a "dynamic" relation
> which establishes relations as Query objects. use lazy="dynamic" for
> this and see the examples in the "Large Collections" portion of the
> documentation.

Which docs do I need to understand what all this means and where do I
find them?

[Michael Bayer]

On Feb 13, 2009, at 2:12 PM, Chris Withers wrote:

>
> Michael Bayer wrote:
>>
>> session
>> .query(Record).with_parent(unit).order_by(Record.date.desc()).first()
>
> OK, this works...
>
>> alternatively, to do exactly what you stated above, i.e.
>> unit.records.order_by()... etc., you can use a "dynamic" relation
>> which establishes relations as Query objects. use lazy="dynamic"
>> for
>> this and see the examples in the "Large Collections" portion of the
>> documentation.
>
> Which docs do I need to understand what all this means and where do I
> find them?

that would be here: http://www.sqlalchemy.org/docs/05/mappers.html?highlight=large%20collections#working-with-large-collections

[Chris Withers]

Michael Bayer wrote:
>
>> Which docs do I need to understand what all this means and where do I
>> find them?
>
> that would be here: http://www.sqlalchemy.org/docs/05/mappers.html?highlight=large%20collections#working-with-large-collections

Cool, so my model now looks like:

Base = declarative_base(engine=engine)

class Unit(Base):

__tablename__ = 'units'

id = Column(Integer,primary_key=True)
name = Column(String)

class Record(Base):

__tablename__ = 'records'

timestamp = Column(DateTime,primary_key=True)
unit_id = Column(Integer,ForeignKey('units.id'),primary_key=True)
unit = relation('Unit',
backref=backref('records',
order_by=timestamp.desc(),
lazy='dynamic'))
is_on = Column(Boolean)
mode = Column(Integer)
error = Column(Integer)
set = Column(Integer)
measured = Column(Integer)

...and I can get the latest record for a unit with:

unit = session.query(Unit).filter(Unit.id==3).first()
unit.records.first()

...which is excellent :-)

Now, how would I get all the records for a particular timeframe for a
unit, ordered oldest to newest? (eg: all the records for today, or all
the records for 08:00-12:00 today)

This needs to be as efficient as possible as each unit generates one
record per minute, so once this has been up for a year, we're talking
about 260,000-ish records, of which I only want, say 1,440 for the whole
day or 240 for 08:00-12:00.