Databases
StackOverflow2013
Postsdbo
POCreating a tree from self referential tables in SQLalchemy
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating a tree from self referential tables in SQLalchemy
    primarykey
    data
    text
    <p>I'm building a basic CMS in flask for an iPhone oriented site and I'm having a little trouble with something. I have a very small database with just 1 table (pages). Here's the model:</p> <pre><code>class Page(db.Model): __tablename__ = 'pages' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(100), nullable=False) content = db.Column(db.Text, nullable=False) parent_id = db.Column(db.Integer, db.ForeignKey("pages.id"), nullable=True) </code></pre> <p>As you can see, for sub pages, they just reference another page object in the <code>parent_id</code> field. What I'm trying to do in the admin panel is have a nested unordered list with all the pages nested in their parent pages. I have very little idea on how to do this. All i can think of is the following (which will only work (maybe—I haven't tested it) 2 levels down):</p> <pre><code>pages = Page.query.filter_by(parent_id=None) for page in pages: if Page.query.filter_by(parent_id=page.id): page.sub_pages = Page.query.filter_by(parent_id=page.id) </code></pre> <p>I would then just format it into a list in the template. How would I make this work with potentially over 10 nested pages?</p> <p>Thanks heaps in advance!</p> <hr> <p><strong>EDIT:</strong> I've looked around a bit and found <a href="http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships" rel="noreferrer">http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships</a>, so I added</p> <pre><code>children = db.relationship("Page", backref=db.backref("parent", remote_side=id)) </code></pre> <p>to the bottom of my <code>Page</code> model. and I'm looking at recursively going through everything and adding it to a tree of objects. I've probably made no sense, but that's the best way I can describe it</p> <hr> <p><strong>EDIT 2:</strong> I had a go at making a recursive function to run through all the pages and generate a big nested dictionary with all the pages and their children, but it keeps crashing python so i think it's just an infinite loop... here's the function</p> <pre><code>def get_tree(base_page, dest_dict): dest_dict = { 'title': base_page.title, 'content': base_page.content } children = base_page.children if children: dest_dict['children'] = {} for child in children: get_tree(base_page, dest_dict) else: return </code></pre> <p>and the page i'm testing it with:</p> <pre><code>@app.route('/test/') def test(): pages = Page.query.filter_by(parent_id=None) pages_dict = {} for page in pages: get_tree(page, pages_dict) return str(pages_dict) </code></pre> <p>anyone got any ideas?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTom Brunoli
    1. USTom Brunoli
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCreating a tree from self referential tables in SQLalchemy
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCreating a tree from self referential tables in SQLalchemy
      1. USISE
      1. VTFavorite
    3. VO
      singulars
      1. POCreating a tree from self referential tables in SQLalchemy
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload