Re: [sqlalchemy] Complex queries and pseudo fields

[Michael Bayer]

On Jul 20, 2012, at 3:37 AM, Sergey Kucheryavski wrote:

Hello,

I want to use a table and ORM for keeping and managing tree of static web pages with MPTT approach. The class/table itself is rather simple:

class Node(db.Model):

    __tablename__ = 'node'

    id = db.Column(db.Integer, primary_key = True)

    parent_id = db.Column(db.Integer, db.ForeignKey(id))

    name = db.Column(db.String(150), nullable = False)

    urlname = db.Column(db.String(150), nullable = False)

    lft = db.Column(db.Integer, nullable = False)

    rgt = db.Column(db.Integer, nullable = False)

here urlname is the part of full URL a page has, e.g. if we have a page Presentations with a list of presentations, with parent page Library and available at '/library/presentations', the 'urlname' for this record will be 'presentations' and urlname for the parent page, Library, will be 'library'. However in this case, every time I need a full URL to the page I have to make a query to get it. In standard SQL I can get a list of pages with pseudo field 'url' as following:

select n1.name, (select group_concat(n2.urlname SEPARATOR '/') from node n2 where n2.lft <= n1.lft  and n2.rgt >= n1.rgt order by n2.lft asc) as url from node n1 

Is there any way to make SQLAlchemy select this pseudo field automatically every time I make a query or fetch an object? Say I do something like n = Node.query.filter(name = 'Presentations') and then just use n.url?  

Thanks a lot in advance for any hints!

you'd get group_concat via func.group_concat(...), the "SEPARATOR" logic you can probably get via op():

from sqlalchemy import func, literal_column

func.group_concat(Node.urlname.op('SEPARATOR')(literal_column('/'))

to hook it into the mapping to be automatically selected use column_property():  http://docs.sqlalchemy.org/en/rel_0_7/orm/mapper_config.html?highlight=column_property#using-column-property-for-column-level-options

since you're looking to fit a whole correlated subquery in there, you'll want to build a select() against aliased:

n2 = aliased(Node)

sel = select([group_concat(...)]).where(n2.lft <= Node.lft).where(n2.rgt >= Node.rgt).order_by(n2.lft).label('url')

Node.url = column_property(sel)

select() will auto-correlate the FROM list when used as a scalar subquery inside of a larger one.

[Sergey Kucheryavski]

Thank you very much, Michael! 

I followed the link you gave and read about @hybrid_property. Seems like I can also use something like 

@hybrid_property

def url(self):

   sel = select([func.group_concat(Node.urlname).label('url')]).where(Node.lft <= self.lft).\

      where(Node.rgt >= self.rgt).order_by(Node.lft)

   return db.session.execute(sel).first()['url'].replace(',', '/')

Is it better or worse alternative to column_property in this case? Thanks a lot in advance!

[Michael Bayer]

On Jul 21, 2012, at 3:23 PM, Sergey Kucheryavski wrote:

Thank you very much, Michael! 

I followed the link you gave and read about @hybrid_property. Seems like I can also use something like 

@hybrid_property

def url(self):

   sel = select([func.group_concat(Node.urlname).label('url')]).where(Node.lft <= self.lft).\

      where(Node.rgt >= self.rgt).order_by(Node.lft)

   return db.session.execute(sel).first()['url'].replace(',', '/')

Is it better or worse alternative to column_property in this case? Thanks a lot in advance!

the hybrid property you have above will work given a Node instance since you're running the select() statement through execute(), but as written wouldn't work in an expression, like "Node.url == 'xyz'", unless you defined an @expression form that produced an inline expression.

I almost always use hybrids, but column_property has the one advantage that it is automatically part of the SELECT emitted whenever you query() for Node.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/RDW-8r8MWdcJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Sergey Kucheryavski]

Then I perhaps did not understand how to use column_property correctly. I mean one can do this:

n2 = aliased(Node)

sel = select([group_concat(...)]).where(n2.lft <= Node.lft).where(n2.rgt >= Node.rgt).order_by(n2.lft).label('url')

Node.url = column_property(sel)

only when a class is defined, after class definition. And in this case it is not possible to use url field in queries. Will try to read documentation more carefully once again. Thanks a lot!

[Sergey Kucheryavski]

Well it works properly, thanks once again!