Custom filter with SQLAlchemy relationship

[shohamc1]

Is it possible to create a filter using properties from a relationship to a model? Say for the models:

from database import Base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
class Foo(Base):
    __tablename__ = 'foo'

    f_id = Column(Integer, primary_key = True)
    name = Column(String)
    bar = relationship('Bar', backref='foo')

class Bar(Base):
    __tablename__ = 'bar'

    b_id = Column(Integer, primary_key = True)
    number = Column(Int)
    f_id = Column(Integer, ForeignKey('foo.f_id'))

I would like to create a filter that might emulate the following:

from graphene import Int
from graphene_sqlalchemy_filter import FilterSet

class FooFilter(FilterSet):
    number = Int()
    class Meta:
            model = Foo
    def number_filter(self, query, value):
            return Foo.Bar.number == value

This doesn't work, but how would I go about to get similar functionality? Any help appreciated!

[art1415926535]

Docs

    def number_filter(self, query, value):
        query = query.join(Foo.bar)
        return query, Bar.number == value

[shohamc1]

This is clearer! Thanks for the help.

[shohamc1]

Just a quick side note: how would I go about doing string matching?

[art1415926535]

value can be of any type. Just use the correct schema. Eg number = Int() -> text = String()

[shohamc1]

No I meant using something like regex or Postgres' LIKE. Is it possible to get all entries with matching text format?

[art1415926535]

Docs

class FooFilter(FilterSet):
   class Meta:
       model = Foo
       fields = {
           'name': ['like', 'ilike'],  # or 'name': [...]
       }

[shohamc1]

I don't think you understood what I was trying to ask. Say for a different set of models

from database import Base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
class Foo(Base):
    __tablename__ = 'foo'

    f_id = Column(Integer, primary_key = True)
    name = Column(String)
    bar = relationship('Bar', backref='foo')

class Bar(Base):
    __tablename__ = 'bar'

    b_id = Column(Integer, primary_key = True)
    text = Column(String)
    f_id = Column(Integer, ForeignKey('foo.f_id'))

I would have a query:

{
    Foo (filters: {text: "%a%"}) {
        edges {
            node {
                name
                Bar {
                    edges {
                        node {
                            text
                        }
                    }
                }
            }
        }
    }

This would filter the contents according to Bar.text, but the filter would still be under Foo.

[art1415926535]

This is not graphql way. Arguments should only affect fields where applied.

Right query:

{
    Foo {
        edges {
            node {
                name
                Bar(filters: {text: "%a%"}) {
                    edges {
                        node {
                            text
                        }
                    }
                }
            }
        }
    }

or

{
    Foo (filters: {barText: "%a%"}) {
        edges {
            node {
                name
                Bar(filters: {text: "%a%"}) {
                    edges {
                        node {
                            text
                        }
                    }
                }
            }
        }
    }

to minimize bugs (in second option), you can use variables to pass a text value.

[shohamc1]

Okay that clears stuff up!

[shohamc1]

For anyone stumbling on this trying the same thing, this is actually possible to achieve although it shouldn't really be your approach. However if you are stuck you can do:

    def text_filter(self, query, value):
        query = query.join(Foo.bar)
        return query, Bar.text.ilike('%{}%'.format(value)

[art1415926535]

I don't understand your use case. 😵