Need assistance with flask sqlalchemy sqlite relationship definition and subsequent query?

Question

I have read and looked at all the sqlalchemy and flasksqlalchemy docs, and I have tried several different iterations of the code below, but I am unable to achieve what I want using flask-sqlalchemy methods.

I have 3 tables defined: Users, Downloads, and ProjectProfiles.

I am trying to pull all download and project profile information based on the "current user".

I can achieve this using the following standard SQL query:

SELECT u.first_name, u.last_name, pp.project_name, pp.project_number, cd.file_name, cd.original_date
FROM users u
INNER join customer_data cd
ON u.client_id = cd.client_id
INNER join project_profile pp
ON cd.project_id = pp.project_id
WHERE u.id = 3
AND cd.original_date > '2019-11-24'

My flask-sqlalchemy models are defined below:

class Users(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    client_id = db.Column(db.String(10), index=True)
    first_name = db.Column(db.String(64))
    last_name = db.Column(db.String(64))
    email = db.Column(db.String(120), index=True, unique=True)
    downloads = db.relationship('Downloads', backref='file_owner', lazy='dynamic')

class Downloads(db.Model):
    __tablename__ = 'client_downloads'
    id = db.Column(db.Integer, primary_key=True)
    client_id = db.Column(db.String(10), db.ForeignKey('users.client_id'), index=True)
    project_id = db.Column(db.String(10), index=True)
    file_type = db.Column(db.String(255))
    file_name = db.Column(db.String(255))
    file_extension = db.Column(db.String(5))
    file_directory = db.Column(db.TEXT())
    original_date = db.Column(db.DateTime)
    project_info = db.relationship('ProjectProfiles', foreign_keys='ProjectProfiles.project_id', backref='pinfo', lazy='dynamic')

class ProjectProfiles(db.Model):
    __tablename__ = 'project_profile'
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.String(10), db.ForeignKey('client_downloads.project_id'), index=True)
    project_name = db.Column(db.String(40))
    project_number = db.Column(db.String(30))
    client_id = db.Column(db.String(10))
    created_date = db.Column(db.DateTime)
    modified_date = db.Column(db.DateTime)

When testing this using flask shell with the following commands I can get the downloads for a specific user, but if I try to get the project profiles associated with the user/downloads, I cant seem to access that info:

Flask shell commands to get downloads:

[2019-12-02 13:49:04,444] INFO in __init__: Example startup
Python 3.6.8 (default, Aug  7 2019, 17:28:10)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
App: app [testing]
Instance: /var/www/testing/instance
>>> from app import db
>>> from app.models import Users, Downloads, ProjectProfiles
>>> from datetime import datetime, timedelta
>>> u = Users.query.get(2)
>>> curr_date = datetime.utcnow()
>>> diff_date = curr_date - timedelta(weeks=2)
>>> dloads = u.downloads.filter(Downloads.original_date > diff_date).all()

Results for dloads:

>>> dloads
[<Downloads 240169>]
>>> for d in dloads:
...     print("project_id:", d.project_id)
...
project_id: 20635

Results when accessing project_info definition/relationship from dloads:

>>> for d in dloads:
...     print(d.project_info)
...
SELECT project_profile.id AS project_profile_id, project_profile.project_id AS 
project_profile_project_id, project_profile.projname AS project_profile_projname, 
project_profile.projnumber AS project_profile_projnumber, project_profile.client_id AS 
project_profile_client_id, project_profile.created_date AS project_profile_created_date, 
project_profile.modified_date AS project_profile_modified_date
FROM project_profile
WHERE ? = project_profile.project_id

Error trying to set projs variable to dloads.project_info:

>>> projs = dloads.project_info.query.all()
Traceback (most recent call last): File "<console>", line 1, in <module>
AttributeError: 'list' object has no attribute 'project_info'
>>>

Successful result if I first return a single value from Downloads and specifically filter/join project_ids:

>>> d = Downloads.query.get(2)
>>> d
<Downloads 2>
>>> projs = d.project_info.filter(ProjectProfiles.project_id == d.project_id).all()
>>> projs
[<ProjectProfiles 3779>]
>>> for p in projs:
...     print("project_name:", p.project_name)
...
project_name: Test Project Profile

Current flask route definition for downloads:

@app.route('/testing', methods=['GET', 'POST'])
@app.route('/testing/index', methods=['GET', 'POST'])
@login_required
def index():
    curr_date = datetime.utcnow()
    diff_date = curr_date - timedelta(weeks=52)
    downloads = current_user.downloads.filter(Downloads.original_date > diff_date).all()
    return render_template('index.html', downloads=downloads, user=current_user)

Even in the above route definition, I am technically only returning the fields specific to downloads, not a combination of downloads and user data like I am used to seeing with a SQL query like shown above.

What I would like to do is define an object/variable that returns download AND project information together in a single object/variable filtered by the current user as shown above!

Any help is much appreciated!

EDIT - Based on accepted answer I came up with the following that worked for me

downloads = db.session.query(Downloads).join(Users, ProjectProfiles).filter(Downloads.original_date > diff_date, Users.id == current_user.id)

Answer 1

Regarding the error, we have to consider that dload is a list not a single download. In addition, project_info is also a list. Therefore depending on your logic, you need to take the first element in the list:

projs = dloads[0].project_info[0].query.all()

If you want to get results from different tables in one result, the result is a list of attributes selected from different tables (not a single sqlalchemy object anymore). At least if you want to have a single statement. For example:

combinedresult = db.session.query(ProjectProfiles.id, Downloads.id, 
    Users.id).filter(Downloads.original_date > diff_date, Users.id==1).all()

selects the ids for projectprofiles, downloads, users limited by your constraint for the date and a specific user.