CODEWITHSUNDEEP
databaseflasksqlalchemylinked-listdoubly-linked-list
Panpaper
Panpaper

Reputation: 551

How to display linked data from database in flask?

I have a table in my database containing a linked list.

ID | page | prior | next
1  | A    | 0     | 2
2  | B    | 1     | 3
3  | C    | 2     | 4
4  | D    | 3     | 5
5  | E    | 4     | 0

How do I display all the items in this table? A SORT BY wouldn't work after a few positional swaps and insertions. I'm using Flask, which uses Jinja2 templates. My current approach is to locate the first item and add it to a list. Then, based on the previous item's "next" value, collect the next row and add it to the list.

num_rows = Pages.query.count()

# find first row, the one where prior is 0
first_row = Pages.query.filter_by(prior=0).first()

# create a list containing just the first row for now
all_rows = [first_row, ]

# add new rows to the list
for i in range(0, (num_rows-1)):
    current_row = all_rows[i].next
    all_rows.append(Pages.query.get(current_row))

Finally, I pass the list to render_template('template.html', all_rows = all_rows), then retrieve it in the template.

Surely there's a more elegant approach? I imagine that this would perform terribly and require lots of resources?

Upvotes: 0

Views: 104

Answers (1)

Chris Johnson
Chris Johnson

Reputation: 21976

It depends on what DBMS you’re using. Oracle has a proprietary CONNECT BY PRIOR syntax that’s very easy to read. Many others have a WITH syntax that’s harder to follow but can accomplish the same result.

See Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER.

Upvotes: 1

Related Questions