CODEWITHSUNDEEP
pythonsqlalchemy
demonLaMagra
demonLaMagra

Reputation: 409

SQL Alchemy query child table filtered by parent join

I'm new to SQL Alchemy and I'm currently developing a performance tool for work but struggling with queries. What I'm trying to do is query a child table where it's parent table matches a specific filter.

It may be that I have my tables set up incorrectly which is not allowing me to do what I want.

Here are my models.

class TestRecord(db.Model):
"""

"""
    id =                    db.Column(db.Integer, primary_key=True)
    test_process =          db.Column(db.String(60))
    test_software_ident =   db.Column(db.VARCHAR(15))
    uut_part_number =       db.Column(db.VARCHAR(30))
    uut_serial_number =     db.Column(db.VARCHAR(30))
    socket_reference =      db.Column(db.VARCHAR(10))
    operator =              db.Column(db.VARCHAR(100))
    date_start =            db.Column(db.Date)
    time_start =            db.Column(db.Time)
    date_end =              db.Column(db.Date)
    time_end =              db.Column(db.Time)
    result =                db.Column(db.VARCHAR(20))
    testset_part_number =   db.Column(db.VARCHAR(10))
    testset_serial_number = db.Column(db.VARCHAR(10))
    testset_cal_date =      db.Column(db.Date)
    tests =                 db.relationship('Test', backref='test_record', lazy='dynamic')

class Test(db.Model):
"""

"""
    id = db.Column(db.Integer, primary_key=True)
    test_record_id = db.Column(db.Integer, db.ForeignKey(TestRecord.id))
    test_block = db.Column(db.VARCHAR(10))
    test_number = db.Column(db.VARCHAR(10))
    test_description = db.Column(db.Text)
    reading = db.Column(db.VARCHAR(20))
    upper_limit_exp = db.Column(db.VARCHAR(20))
    lower_limit = db.Column(db.VARCHAR(20))
    units = db.Column(db.VARCHAR(20))
    result = db.Column(db.VARCHAR(20))

What I've tried to implement in these tables is that 1 TestRecord can have many Tests.

and the query I'm trying to run is for each test where its test_number is equal to "START" and its parent.uut_part_number is equal to a value.

current query I have is:

records = Test.query.join(Test.test_record_id, aliased=True).filter_by(test_number="/START")
                               .filter(TestRecord.uut_part_number == session['part_number'])

I've been reading the SQL Alchemy docs and no matter what combination I try, I can not seem to get the result I'm after.

Upvotes: 3

Views: 3622

Answers (1)

Matt Healy
Matt Healy

Reputation: 18531

How about this:

records = Test \
    .query \
    .join(TestRecord) \
    .filter(Test.test_number == "/START") \
    .filter(TestRecord.uut_part_number == session['part_number'])

This will give you all the Test records where the Test.test_number is /START, and by joining with TestRecord, you can filter by records where TestRecord.uut_part_number matches the input part_number.

You can join Test with TestRecord simply because there is only one foreign key field between Test and TestRecord so SQLalchemy knows how to join the tables.

Upvotes: 5

Related Questions