My app Model is structured as so:
user_jobs = db.Table('user_jobs',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('job_id', db.Integer, db.ForeignKey('market.id'))
)
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
# Other user model fields....
jobs = db.relationship('Job', secondary=user_jobs, backref='users')
class Job(db.Model):
id = db.Column(db.Integer, primary_key=True)
# Other related fields and relationships
quotes = db.relationship('Quote', backref='job', lazy='dynamic')
class Quote(db.Model):
id = db.Column(db.Integer, primary_key=True)
timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
price = db.Column(db.Integer())
# Other related fields
job_id = db.Column(db.Integer, db.ForeignKey('job.id'))
This model allows users to follow multiple jobs while jobs can have multiple followed users (Many to Many). A job can have multiple Quotes (One to Many).
In my flask app, I am creating a dashboard that displays the users followed jobs. For the followed jobs on the dashboard, I want to display the most recent Quote price and timestamp.
My current thinking is to create a function on the user model to return a joined table of User - Job - Quote, ordering by desc and limit(1). I however am stuck on how to do this.
class User(UserMixin, db.Model):
.....
def get followed_jobs(self):
return ...
Any help would be greatly appreciated.
EDIT:
Given there is a list of users and I'm trying to find the latest quotes that user 1 is following, the raw SQL appears to be:
Select
*
FROM
(
SELECT
job.id, job.job_name, latest_quote.timestamp,
latest_quote.price, user_job.user_id
FROM
(SELECT
job_id, max(timestamp) AS timestamp,
price FROM quote
GROUP BY job_id) AS latest_quote
JOIN
job
ON
job.id = latest_quote.job_id
JOIN
user_job
ON
user_job.job_id = latest_quote.job_id
) as aquery
WHERE user_id = 1;
Can this be made more efficient in SQL?
