I have the following Models/Tables in falsk-sqlalchemy
.:
class MainTerm(db.Model):
__tablename__ = 'main_term'
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String)
terms_in_gzt = db.Table('terms_in_gzt',
db.Column('gzt_term_id', db.Integer, db.ForeignKey('gzt_term.id')),
db.Column('gazetteer_id', db.Integer, db.ForeignKey('gazetteer.id')),
)
class Gazetteer(db.Model):
__tablename__ = 'gazetteer'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
terms = db.relationship('GztTerm', secondary=terms_in_gzt, backref='gazetteers', lazy='dynamic')
class GztTerm(db.Model):
__tablename__ = 'gzt_term'
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String)
I'm currently running sqlite (sqlite version '3.32.3'), but it is later intended to use postgresql. So a solution for postgres would be more important than for sqlite.
And I have this join query that queries the text
column of GztTerm
entries that are associated with Gazetteer.id == 3
:
joinq = db.session.query(GztTerm.text).join(terms_in_gzt).join(Gazetteer).filter(Gazetteer.id == 3)
joinq.all()
With the following result:
[('IBM'), ('Lund'), ('Bluetooth')]
When I now call:
binary_expression = MainTerm.text.contains(joinq)
res = db.session.query(MainTerm).filter(binary_expression).all()
Then I get only results with the first element "IBM". The results with "Bluetooth" or "Lund" are missing, even though they are in the database. When I change the query, I always get only results for the first element of the joinq
.
So my question is:
What do I need to change, that my BinaryExpression MainTerm.text.contains(joinq)
considers all the elements of the joinq
?
Thanks in advance!
Edit:
The following code for constructing the binary_expression
archives the desired behaviour:
from sqlalchemy import or_
binary_expression = or_(MainTerm.text.contains(text) for text in joinq)
However, this feels more like a hack and is probably not that efficient.
Is there some way to archive the same behaviour without loop only using sqlalchemy
?