Well it works properly, thanks once again!
On Sunday, July 22, 2012 9:01:29 AM UTC+2, Sergey Kucheryavski wrote:
>
> 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!
>
> On Saturday, July 21, 2012 10:04:23 PM UTC+2, Michael Bayer wrote:
>>
>>
>> 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/-/ywNpefZ89LkJ.
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.
