seizetheday seizetheday - 6 months ago 32
Python Question

sqlalchemy joinedload query can not make counting

I have two tables

table_a
and
table_b
.
table_b.code_b
is the foreign key of the
table_a


table_a
code_a, val
-----------
aaa,100

table_b
code_b, name
------------
aaa, name1


I want to get this result with joining two tables:

table_result
code_a,val,name
---------------
aaa,100,name1


The following is the Python code:

class A(db.Model):
__tablename__ = 'table_a'
code_a = Column(String(20), ForeignKey("table_b.code_b"), primary_key=True)
val = Column(Float)
b = relationship("B", backref=backref('table_b'))


class B(db.Model):
__tablename__ = 'table_b'
code_b = Column(String(20), primary_key=True)
name = Column(String(100))
a = relationship("A", backref="table_a")

where_str = "code_b='aaa'"
q = A.query.options(joinedload_all(A.b)).filter(text(where_str))

rows1 = [i.serialize for i in q.all()] # CORRECT
rows2 = [i.serialize for i in q.paginate(1,10,False)] # ERROR


If I just query all to get
rows1
, it's correct result. However, I will get error with running
rows2
:
DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: "code_b"

I debug this error with print the SQL statement:

SELECT count(*) AS count_1
FROM (SELECT "table_a".code_a AS "table_a_code_a",
FROM "table_a"
WHERE code_b = 'aaa') anon_1


It's clear that two tables are NOT joined.

But, If just
q.all()
, SQL statement will be:

SELECT "table_a".code_a AS "table_a_code_a",
FROM "table_a" LEFT JOIN "table_b"
WHERE code_b = 'aaa'


This is correct.

So, How to get the right result when making the pagination.




EDIT1:

Q: Why I use
where_str
?


A: In HTML page, I use the query builder(http://querybuilder.js.org/) to get query filter(maybe complicated). So, it's convenient to pass to SQLAlchemy query filer.

There are any better ways to implement this?




EDIT2:
Solution:

q = A.query.join(A.b).options(contains_eager(A.b)).filter(text(where_str))

Answer

This is incorrect:

where_str = "code_b='aaa'"
q = A.query.options(joinedload_all(A.b)).filter(text(where_str))

How is SQLAlchemy supposed to know that the code_b in your where_str is supposed to refer to table B?

Try:

q = A.query.join(A.b).filter(B.code_b == "aaa")

If you also need to perform a joinedload, do this:

q = A.query.join(A.b).options(contains_eager(A.b)).filter(B.code_b == "aaa")
Comments