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