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. > > On Friday, July 20, 2012 4:00:17 PM UTC+2, Michael Bayer wrote: > > 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. > > > > -- > 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 sqlalchemy@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 sqlalchemy@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.