sqlalchemy .contains() matches only first element

user_7483494 0
Viewed 347

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?

flask-sqlalchemy sqlalchemy python
1 Answers

I ended up adding a column_property to GztTerm:

from sqlalchemy.orm import column_property

class GztTerm(db.Model):
    __tablename__ = 'gzt_term'
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String)
    text_like = column_property('%'+text+'%')

This enables me to use like instead of contains (which itself is based on like) together with any_ (Thanks to Ilja Everilä for this idea!).

The final query looks like this:

from sqlalchemy import any_
joinq  = db.session.query(GztTerm.text_like).join(terms_in_gzt).join(Gazetteer).filter(Gazetteer.id == 3)
binary_expression = MainTerm.text.like(any_(joinq))
res = db.session.query(MainTerm).filter(binary_expression).all()

To get the complement notlike and all_ is used for the binary expression:

from sqlalchemy import all_
binary_expression = MainTerm.text.notlike(all_(joinq))

Hopefully this is helpful to someone!

user_74834940
Related