CODEWITHSUNDEEP
sqlalchemyflask
Craig
Craig

Reputation: 151

SQL alchemy query filter syntax is not working

Here are the models I am working with:

class User(db.Model):
__tablename__ = 'user'
uid = db.Column(db.Integer, primary_key=True, index=True)
firstName = db.Column(db.String(100))
lastName = db.Column(db.String(100))
emailAddress = db.Column(db.String(120), unique=True, index=True)
pwHash = db.Column(db.String(256))
userLevel = db.Column(db.Integer())
userAccountType = db.Column(db.Integer())
isUserActive = db.Column(db.Boolean())
isUserLockedOut = db.Column(db.Boolean())
userLastLogin = db.Column(db.DateTime())
lastInvalidLogin = db.Column(db.DateTime())
userCreatedAt = db.Column(db.DateTime())
userConfirmedAt = db.Column(db.DateTime())
userUpdatedAt = db.Column(db.DateTime(), onupdate=datetime.datetime.now())
userAddress = db.relationship('Address', backref='user', lazy='dynamic')
userContactMethod = db.relationship('UserContactMethod', backref='user', lazy='dynamic')
userSensor = db.relationship('Sensor', backref='user', lazy='dynamic')
userReading = db.relationship('Reading', backref='user', lazy='dynamic')
deliveryEvents = db.relationship('logSMTPDeliveryEvents', backref='user', lazy='dynamic')

class Reading(db.Model):
    __tablename__ = 'reading'

rid = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.Integer, db.ForeignKey('user.uid'))
sid = db.Column(db.Integer, db.ForeignKey('sensor.sid'))
readingTimestamp = db.Column(db.DateTime())
readingLightValue = db.Column(db.Integer)
readingLightStatus = db.Column(db.String(6))
readingTemp1 = db.Column(db.Float)
readingTemp2 = db.Column(db.Float)
readingHumidity = db.Column(db.Float)

So my table of readings has the User Id set as the foreign key in the readings table. Now when I try and issue a query like this:

queryResult = db.session.query(Reading).filter(Reading.uid == User.uid)

I get all the rows, which is incorrect. How should I be constructing this query?

Thanks! C

Upvotes: 0

Views: 678

Answers (1)

SingleNegationElimination
SingleNegationElimination

Reputation: 156268

It's not clear what you're trying to filter out from your question; Are you trying to find the Reading rows that correspond to a particular User row?

Supposing you have the email address of a user, and want to find the Reading's that belong to that user, you would need to build your query in three steps:

First, Start with a query that returns rows out of Reading:

q = session.query(Reading)

Next, extend the query to say that you want follow the user link to attributes of User.

q = q.join(Reading.user)

Finally Filter out only the rows that have the desired User features. Make sure you're filtering on a concrete, actual value.

q = q.filter(User.emailAddress == '[email protected]')

Upvotes: 2

Related Questions