Thanks Mike.
On Sun, Oct 16, 2016 at 7:14 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
>
> On 10/14/2016 06:08 PM, Jinghui Niu wrote:
>
>> I have the following Table model representing a timeline.
>>
>> |
>> classTimeRange(Base):
>>
>>
>> __tablename__ ="time_line"
>>
>>
>> record_id =Column(Integer,primary_key=True)
>> level =Column(String,nullable=False)# e.g. "Point", "Range"
>> content =Column(String,nullable=False)
>> language_marker =Column(String)# this one column is optional and
>> needs to be queried
>> immediate_parent_id =Column(Integer,ForeignKey('ti
>> me_line.record_id'))
>> child_timelines
>> =relationship('TimeRange',backref=backref('parent_timeline',
>> remote_side=[record_id]))
>> |
>>
>>
>> The language_marker Column is the one that needs to be queried in a
>> recursive manner. Not all records have such an attribute, and the
>> business logic is: along the hierarchy lineage from the root down to the
>> child timelines, at least one level of the TimeRange instance carries
>> such an attribute, and the one in the lowest level should be returned.
>> This works a little like cascading style sheet, where if the TimeRange
>> object itself doesn't have such an attribute, just look further up one
>> level above, util found one, and the latest defined style wins.
>>
>> What is the technical direction I should look into to implement such
>> queries? I'm using SQLAlchemy and the backend is SQLite. Thanks.
>>
>
> Someone else can probably work out the details on this one, but the
> general technique on the SQL side is to use a recursive query. With
> Postgresql / SQL Server this is a CTE using WITH RECURSIVE, with Oracle I
> *think* they support this syntax also now though historically it's been
> "CONNECT BY", and then with any other DB like MySQL / SQlite it's basically
> nothing.
>
> The other technique, which I tend to prefer if it can be made feasible, is
> that if I'm working with overall a limited number of rows in the first
> place, such as all of these records where language_marker may be
> significant all belong to some common "document id" or something where
> there are only a few hundred or a few thousand rows that would matter for
> the whole operation I'm doing, I pull it into memory and assemble it into a
> tree hierarchy right there.
>
>
>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> --- You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/to
> pic/sqlalchemy/uUhcft4S7-E/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
