Gazetteer based filtering / Filter dependent on another class

[maxtrem]

Hi,

I'm listing the following table in my datatable (simplified version):

class MainTerm(db.Model):
    __tablename__ = 'main_term'
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String)

Further I have gazetteers that I would like to use for filtering:

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 would like to be able to show or exclude MainTerm entries that share the same text attribute with GztTerm given a certain Gazetteer.

So, when having Gazetteer with id 1. I want to be able to exclude or show all MainTerms that share the same text attribute with one of the GztTerms in Gazetteer.query.get(1).terms.
In short: Filter all MainTerm entries with MainTerm.text == GztTerm.text for all GztTerm in Gazetteer.query.get(1).terms

What I have tried so far is creating a "custom"-filter:

def gzt_filter(expr, value):    
    if value == "show":
        return expr == GztTerm.text
    elif value == "exclude":
        return expr != GztTerm.text
    return expr

from datatables import search_methods 
search_methods.SEARCH_METHODS['gzt_filter'] = gzt_filter

columns = [
...
        ColumnDT(MainTerm.text, search_method='gzt_filter'),
...
]

This works for the show part in that sense that only those MainTerms are shown that share the text attribute with anyGztTerm. But it does not enable me to filter GztTerm in a certain Gazetteer instance.

Another problem with the above shown example is that when using exclude, the MainTerm entries that are shown in the table are multiplied with the number of GztTerm entries. So, when having 5 GztTerm entries, then each MainTerm entry is duplicated 5 times in the table (except those that were filtered out). Which of course is not desired.

The respective queries for the result that I desire would be:

search_value = 1
q = db.session.query(MainTerm) \
    .filter(MainTerm.text == GztTerm.text)\
    .filter((terms_in_gzt.c.gzt_term_id == GztTerm.id) & (terms_in_gzt.c.gazetteer_id == search_value))

# respectively
q = db.session.query(MainTerm) \
    .filter(MainTerm.text != GztTerm.text)\
    .filter((terms_in_gzt.c.gzt_term_id == GztTerm.id) & (terms_in_gzt.c.gazetteer_id == search_value))

Where search_value is given by the user and handed over to ColumnDT.

Is there any way to archive this filtering behaviour with datatables?

Thanks a lot!

[tdamsma]

Don't know if I understood the question, but you can query and filter results combined from multiple tables by joining them