[Tutor] flask_sqlalchemy query in relation to SQL relationships.

[mhysnm1964 at gmail.com]

All,

 

Python 3.6 under windows 10 - I am using flask_sqlalchemy   and finding it
quite good with taking a lot of the heavy lifting of writing SQL statements.
I have a question in relation to SQL relationships and how would this be
done using SQL or flask_sqlalchemy or sqlalchemy.

 

I have three tables which are related to each other.

 

Table 1 has many records related to table 2.

Table 2 has many relationships to table 3.

 

Below is the model used by flask_sqlalchemy to make things clearer:

 

 

class Categories(db.Model):

# one to many relationships to SubCategories 

    id = db.Column(db.Integer, primary_key=True)

    category  = db.Column(db.String(64), index=True, unique=True)

    subcategories  = db.relationship('SubCategories', backref='categories',
lazy='dynamic')

    

    def __repr__(self):

        return '<Categories {}>'.format(self.category)

 

class SubCategories(db.Model):

# Many to one relationship to Categories.

# One to many relationship with Transactions.

    id = db.Column(db.Integer, primary_key=True)

    subcategory  = db.Column(db.String(80), index=True, unique=True)

    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))

    transactions   = db.relationship('Transactions',
backref='sub_categories', lazy='dynamic')

 

class Transactions (db.Model):

# Many to one relationship to Sub_categories 

# Many to one relationships with Accounts. 

    id  = db.Column(db.Integer, primary_key=True)

    transactiondate = db.Column(db.Date, index=True, nullable=False)

    description  = db.Column(db.String(80), nullable=False)

    amount = db.Column(db.Float, nullable=False) 

    subcategory_id = db.Column(db.Integer,
db.ForeignKey('sub_categories.id'))

    account_no  = db.Column(db.Integer,
db.ForeignKey('accounts.account_number'))

 

Thus, when I have a query from the view:

 

records = Transactions.query.order_by(Transactions.subcategory_id,
Transactions.transactiondate.desc())

 

page = request.args.get('page', 1, type=int)

    records  = records.paginate(page, app.config['POSTS_PER_PAGE'], False)

    next_url = url_for('index', page=records.next_num) if records.has_next
else None

    prev_url = url_for('index', page=records.prev_num) if records.has_prev
else None

    return render_template('index.html', title='Budget Program Main Page',
records = records.items, tables = tables, account  = 'all Transactions',
prev_url = prev_url, next_url = next_url, form = form, sort_form =
sort_form)

 

Template HTML code which displays the category and sub_category text values
based upon the above query. 

        {% for row in records %}

            <tr>

                <td>{{row.accounts.account_name}}</td>

                <td>{{row.account_no}}</td>

                <td>{{row.transactiondate}}</td>

                <td>{{row.description}}</td>

                <td>{{row.amount}}</td>

                <td>{{row.sub_categories.categories.category}}</td>

                <td>{{row.sub_categories.subcategory }}</td>

            </tr>

        {% endfor %}

 

What I cannot do, is use     sub_categories.categories.category or
sub_categories.subcategory in the query statements to sort the transaction
table by category or sub_category . For example the following does not work

 

sort_by = Transactions.sub_categories.categories.category

records = Transactions.query.order_by(sort_by.desc())

 

sqlalchemy  complains that it cannot find the object .categories. Thus I do
not know how to sort on the category table which is a child of
sub_categories which is a child of transactions. How would this be done in
SQL?

 

I hope this is the right place for this question.

 

Sean