Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Ticket #1582 (closed enhancement: fixed)

Opened 10 years ago

Last modified 9 years ago

[PATCH] Paginate should allow ordering by attributes from related objects (SA Query)

Reported by: roger.demetrescu Owned by: roger.demetrescu
Priority: normal Milestone: 1.0.4
Component: TurboGears Version: 1.0.4b1
Severity: normal Keywords: paginate sqlalchemy query
Cc:

Description

The current implementation from paginate + Query (sqlalchemy) doesn't allow ordering by attributes/properties from related objects.

Eg.: suppose we have the following model:

occupations_table = Table('occupations', metadata,
    Column('occupation_id', Integer, primary_key=True),
    Column('occupation_name', String(20)),
)


users_table = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(20)),
    Column('occupation_id', Integer, ForeignKey("occupations.occupation_id")),
)


addresses_table = Table('addresses', metadata,
    Column('address_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("users.user_id")),
    Column('street', String(50)),
    Column('city', String(40))
)


class Occupation(object):
    pass

class User(object):
    pass

class Address(object):
    pass


mapper(Occupation, occupations_table)

mapper(User, users_table, properties={
    'occupation' : relation(Occupation, lazy=False)
})

mapper(Address, addresses_table, properties={
    'user' : relation(User, backref='addresses', lazy=False)
})

We should be able to have Paginate ordering a Query(Address) by:

  • street
  • city
  • user.user_name
  • user.occupation.occupation_name

See also the following thread on ML:  http://tinyurl.com/2dpqvt

Attachments

sorting.patch Download (2.4 KB) - added by roger.demetrescu 10 years ago.
demo.zip Download (87.7 KB) - added by roger.demetrescu 10 years ago.
A demo project

Change History

Changed 10 years ago by roger.demetrescu

Changed 10 years ago by roger.demetrescu

A demo project

comment:1 Changed 10 years ago by roger.demetrescu

  • Status changed from new to closed
  • Resolution set to fixed

Commited at [3525] and [3526].

comment:2 Changed 10 years ago by roger.demetrescu

  • Status changed from closed to reopened
  • Resolution fixed deleted

The current patch (already applied) doesn't handle backrefs. We should change the code:

mapper.properties.get(propname)

into:

mapper.get_property(propname, raiseerr=False)

or even:

mapper.get_property(propname, resolve_synonyms=True, raiseerr=False)

For more info, take a look at:  http://tinyurl.com/38w4l5

comment:3 follow-up: ↓ 4 Changed 10 years ago by joelpearson

Thanks, Roger! This patch greatly improves the usefulness of paginate.

It worked best for me once I included the change suggested in the previous comment:

mapper.get_property(propname, resolve_synonyms=True, raiseerr=False)

Without "resolve_synonyms=True", mapper synonyms didn't work. With it, everything works as expected.

comment:4 in reply to: ↑ 3 Changed 10 years ago by roger.demetrescu

Replying to joelpearson:

Without "resolve_synonyms=True", mapper synonyms didn't work. With it, everything works as expected.

Hei Joel...

Thanks for your feedback... I'll commit this change as soon as I finish writing paginate tests.

comment:5 Changed 9 years ago by roger.demetrescu

  • Owner changed from anonymous to roger.demetrescu
  • Status changed from reopened to new

comment:6 Changed 9 years ago by roger.demetrescu

One more reason to start using mapper.get_property() instead of mapper.properties.get(): SQLAlchemy 0.4.1 now raises this exception when we try to use the mapper.properties.get():

NotImplementedError: Public collection of MapperProperty 
objects is provided by the get_property() and iterate_properties accessors.

comment:7 Changed 9 years ago by roger.demetrescu

  • Status changed from new to closed
  • Resolution set to fixed

Fixed in [3716] and [3717].

Note: See TracTickets for help on using tickets.