sql alchemy closure table relationship definition

areas = relationship('DbArea',
   backref = backref('routes', order_by = 'DbRoute.name'),
   secondary = area_relationship_table,
   primaryjoin = area_id == area_relationship_table.c.descendent,
   secondaryjoin = DbArea.area_id == area_relationship_table.c.ancestor,
   innerjoin = True, order_by = DbArea.name,
   foreign_keys = [area_relationship_table.c.ancestor,
      area_relationship_table.c.descendent
   ])

Thanks again
for your prompt replies.
sir, i discover that the code about have some errors but i dont how to figure it out.
replies to comments only take the comment.id of the first comment.
ie.i have two comments.when i write a reply on the first i got path = 1.3.when i write reply on the second comment, instead of getting path = 2.4, i got 1.4 and it displays directly under the first comment.what could be problems

@Harvey: it 's difficult for me to say. I think you need to use a debugger to discover what the problem in your logic is. PyCharm and Visual Studio Code both have very good debuggers that work great with Flask applications.

Hi, Miguel
I am a newbie trying to create a little blog using your mega tutorial samples. i am trying to implement nested comments and i found this tutorial written by you also. after going through it and comments from others here, I put up this code into my work similar to something i found here. i will show the routes.py and the reply.html because this is where i fell the error is coming from.
ROUTES.py
@main.route('/blog//', methods=['GET', 'POST'])
def detail(slug):
    post = Post.query.filter_by(slug=slug).first_or_404()
    comment = Comment.query.filter_by(post_id=post.id).order_by(Comment.path.asc()).all()
    form1 = CommentForm()
    form2 = ReplyForm()
    if form2.validate_on_submit() and request.method == 'POST':
        for reply in comment:
            comm = Comment(body=form2.reply.data, author=current_user._get_current_object(),
                                                parent=reply)
            comm.save()
            flash('Your reply has been published')
    elif form1.validate_on_submit() and request.method == 'POST':
  comm=Comment(body=form1.body.data,post=post,author=current_user._get_current_object())
        comm.save()
flash('Your comment  has been published')
        return redirect(url_for('main.detail', slug=post.slug, page=-1))
    page = request.args.get('page', 1, type=int)
    if page == -1:
        page  = (post.comments.count() - 1) // current_app.config['COMMENTS_PER_PAGE'] + 1
    pagination = post.comments.order_by(Comment.timestamp.asc()).paginate(page, 
                                        current_app.config['COMMENTS_PER_PAGE'],False)
    comments = pagination.items
    return render_template('detail.html',post=post, form1=form1,form2=form2,comments=comments,comment=comment,
                           pagination=pagination)
reply.html

{% for reply in comment.replies %}

                {% if moderate or not comment.disabled %}
                    {% if reply.body_html %}
                        {{ reply.body_html | safe }}
                {% else %}
                        {{ reply.body }}
                    {% endif %}
                    {% endif %}
    </div>

{% endfor %}

i wrote two comments to a post, it was ok. but when i write to reply through  the reply form under the first comment, the reply appeared under both comment one and two. 
please how do i force each  reply to sit directly under its comment.

@Mavis: the code you copied from the comments in this article is not a good model to follow, and is not endorsed by me.Creating a web page that accepts threaded comments is actually fairly difficult, you need some JavaScript in addition to the backend support.I intend one day to write a follow up article in which I cover the user interface implementation
for threaded comments, unfortunately until then I do not know any resource to recommend on this topic.

Another approach to the problem is using the logic you described in Adjacency Lists code, but executing it not with multiple requests, but with one recursive request as in this StackOverflow issue: https: //stackoverflow.com/questions/3699395/find-parent-recursively-using-query. This solution, however, requires some understanding of SQL. I can see one comment talking about recursive CTE, but the link should provide an example for those considering it.

@Andriy: recursive queries are not supported by all databases, and I 'm also unclear on the performance implications of using them. Clearly they have to perform better than issuing the recursive queries from the Python application, but is that good enough or can it still perform poorly? I do not know.

>>> from sqlalchemy
import create_engine
   >>>
   engine = create_engine('sqlite://', echo = False)

>>> df = pd.DataFrame({
      'name': ['User 1', 'User 2', 'User 3']
   }) >>>
   df
name
0 User 1
1 User 2
2 User 3

>>> df.to_sql('users', con = engine)
3
   >>>
   engine.execute("SELECT * FROM users").fetchall()[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]

>>> with engine.begin() as connection:
   ...df1 = pd.DataFrame({
      'name': ['User 4', 'User 5']
   })
   ...df1.to_sql('users', con = connection, if_exists = 'append')
2

>>> df2 = pd.DataFrame({
      'name': ['User 6', 'User 7']
   }) >>>
   df2.to_sql('users', con = engine, if_exists = 'append')
2
   >>>
   engine.execute("SELECT * FROM users").fetchall()[(0, 'User 1'), (1, 'User 2'), (2, 'User 3'),
      (0, 'User 4'), (1, 'User 5'), (0, 'User 6'),
      (1, 'User 7')]

>>> df2.to_sql('users', con = engine, if_exists = 'replace',
   ...index_label = 'id')
2
   >>>
   engine.execute("SELECT * FROM users").fetchall()[(0, 'User 6'), (1, 'User 7')]

$ git clone https: //gist.github.com/coleifer/7f3593c5c2a645913b92 closure

$ gcc - g - fPIC - shared closure.c - o closure.so

$ sqlite3
SQLite version 3.8 .7 .1 2014 - 10 - 29 13: 59: 56
Enter ".help"
for usage hints.
Connected to a transient in -memory database.
Use ".open FILENAME"
to reopen on a persistent database.

sqlite > .load. / closure

CREATE TABLE category(
   id INTEGER NOT NULL PRIMARY KEY,
   name VARCHAR(255),
   parent_id INTEGER,
   FOREIGN KEY(parent_id) REFERENCES category(id));

CREATE VIRTUAL TABLE category_closure USING transitive_closure(
   tablename = "category",
   idcolumn = "id",
   parentcolumn = "parent_id");

INSERT INTO category(name, parent_id) VALUES('Books', NULL);
INSERT INTO category(name, parent_id) VALUES('Fiction', 1);
INSERT INTO category(name, parent_id) VALUES('Sci-fi', 2);
INSERT INTO category(name, parent_id) VALUES('Western', 2);
INSERT INTO category(name, parent_id) VALUES('Non-fiction', 1);

sqlite > SELECT name FROM category WHERE id IN(
   ... > SELECT cc.id
   ... > FROM category_closure AS cc
   ... > WHERE cc.root = 1 AND cc.depth = 1);

name
-- -- -- -- -- -
Fiction
Non - fiction

from sqlalchemy
import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative
import declarative_base
Base = declarative_base()
from sqlalchemy.orm
import relationship

class Customer(Base):
   __tablename__ = 'customers'

id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'

id = Column(Integer, primary_key = True)
custid = Column(Integer, ForeignKey('customers.id'))
invno = Column(Integer)
amount = Column(Integer)
customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)

CREATE TABLE invoices(
   id INTEGER NOT NULL,
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   PRIMARY KEY(id),
   FOREIGN KEY(custid) REFERENCES customers(id)
)