Filter options on all connections about graphene-sqlalchemy-filter HOT 33 CLOSED

Hi @docelic! You mean filtration nested connections?

For example

{
  allUsers {
    edges {
      node {
        username
        groups(filters: {nameIn: ["python", "graphql"]}) {
          edges {
            node {
              name
            }
          }
        }
      }
    }
  }
}

from graphene-sqlalchemy-filter.

@art1415926535 thanks for the answer. I actually almost made it work using standard graphene-sqlalchemy options. I will post my solution here during the day so that we can discuss it and find a way to solve the remaining issues. Thanks!

from graphene-sqlalchemy-filter.

Hey @art1415926535, @frenchtoast747, here's my current approach which almost works. I hope I've described it in detail and that we can combine our insights to get this fully working out of the box.

0. Let's start with two ORM models, ModelA and ModelB. Let's say that ModelA has a one-to-many relationship to ModelB (the type of relationship doesn't matter, it can be 1TM or MTM):

class ModelA(db.Model):
  model_bs = db.relationship('ModelB')

1. In GraphQL, graphene-sqlalchemy will automatically find all ORM model relationships in the application and expose them via GraphQL. How exactly it will do this is controllable with options use_connection, connection_class, and connection_field_factory. For example:

class GModelA(SQLAlchemyObjectType):
  class Meta:
    model = ModelA
    use_connection = True # This is default
    connection_class = graphene.Connection or graphene.relay.Connection # This is default
    connection_field_factory = default_connection_field_factory # This is default

class GModelB(SQLAlchemyObjectType):
  class Meta:
    model = ModelB
    use_connection = True # This is default
    connection_class = graphene.Connection or graphene.relay.Connection # This is default
    connection_field_factory = default_connection_field_factory # This is default

So, when we query { modelA(id:...) { modelBs { ... } } in GraphQL, the field modelBs will be a connection.

2. Also important to know is that as part of this process, graphene-sqlalchemy will automatically create Connection classes. For example, with above example, it will automatically create GModelAConnection and GModelBConnection, and if a person tries to manually create them, it will cause a conflict in the GraphQL schema. So, the best approach is to use the connection classes that graphene-sqlalchemy generates automatically for us.

3. Now, we are not happy with those automatically generated connections, because by default graphene-sqlalchemy will create them as SQLAlchemyConnectionFields rather than FilterableConnectionFields.

This is where we use the option connection_field_factory to define our own function which will be used to create connections, and where we can make them be FilterableConnectionFields with appropriate filters:

(You'll notice that I use custom function which returns the filter object, but I think you made it possible to specify filters: in the Meta class for this automatically. If so, please tell how this would be used/accessed automatically from the field_factory function.)

import graphene
import graphene_sqlalchemy
from sqlalchemy import and_, or_
from graphene_sqlalchemy import SQLAlchemyConnectionField, SQLAlchemyObjectType
from graphene_sqlalchemy_filter import FilterableConnectionField, FilterSet

# Based on object class, returns the appropriate filter class
def filter_for(model_type):
  if model_type == GModelA:
    return(GModelAFilter())
  elif model_type == GModelB:
    return(GModelBFilter())
  else:
    raise(Exception('Unknown model_type %s; extend filter_for() to solve it' % str(model_type)))

# Creates connection field using FilterableConnectionField rather than the default SQLAlchemyConnectionField
# You will notice use of ._meta.connection. This uses the connection classes which are
# automatically generated by graphene-sqlalchemy.
def field_factory(relationship, registry, **field_kwargs):
    model = relationship.mapper.entity
    model_type = registry.get_type_for_model(model)
    return FilterableConnectionField(model_type._meta.connection, filters=filter_for(model_type), **field_kwargs)

# And we update GModelA and GModelB to use our field_factory() function. Example for ModelB:
class GModelBFilter(FilterSet):
  class Meta:
    model = ModelB
    fields = {
      'name': [...],
    }
class GModelB(SQLAlchemyObjectType):
  class Meta:
    model = ModelB
    connection_field_factory = field_factory

# Also, we can make modelBs queryable directly from the root query as usual, but also
# by using the automatically-generated connections instead of writing our own:
class Query(ObjectType):
    model_bs = FilterableConnectionField(ModelB._meta.connection, filters=filter_for(ModelB))

And this is (almost!) it. When the app is started and GraphiQL client is reloaded, it will properly show the sort: and filters: options everywhere where a ModelB connection is used.

4. This works fantastically well for top-level fields which we manually define, like for that model_bs mentioned above. However... for nested connections, when we call modelA(id: ...) { modelBs { ... }}, a couple related problems become obvious:

4.1) First, from the GraphQL perspective, we think we are working with true connections with cursors etc.
However, the default lazy= value for all db.relationship()s is lazy=select, which means that our modela.model_bs() function will fetch all the data from the database at once, so we don't really have connections which fetch data from the database in separate parts.

4.2) Second, we can notice that the data set we receive is neither sorted nor filtered, and none of the sorting or filtering options have been applied. This is happening because in graphene-sqlalchemy there is a function UnsortedSQLAlchemyConnectionField.resolve_connection which isn't overriden by graphene-sqlalchemy-filter.

This function runs cls.get_query (the thing which we want) only if resolved value is None. In our case the value of resolved won't be None but rather an InstrumentedList with the query results already in it. (This type and the data is what we get when a db.relationship is defined with the default value of lazy='select').

So, because graphene-sqlalchemy-filter only overrides get_query, which is never called, no sorting or filtering really gets applied.

4.3) Furthermore, even if cls.get_query would be called from resolve_connection, it would be wrong to call it on cls (which has the value of ModelB in our example), because this would filter through all ModelB records, instead of only through those ModelBs that belong to ModelA.

So, how to solve this and make it work in a well-defined way out of the box:

4.4) One possible solution which I have found for this problem (without going into too much of changes) would be to define some DB relationships with lazy='dynamic':

  model_bs = db.relationship('ModelB', lazy='dynamic')

(On python level this has a consequence of having to call modela.model_bs.all() instead of just modela.model_bs to get the actual results, but I am assuming that someone wanting to do this accepts that.)

For relationships which are defined in this way, the function resolve_connection() doesn't get an InstrumentedList(results), but gets a BaseQuery object.

So, maybe graphene-sqlalchemy-filter could override resolve_connection() to detect this, and to apply appropriate sorting and filtering to the BaseQuery object. And from there it should work smoothly!

Comments? Is it something that you Artem would be willing to add?

from graphene-sqlalchemy-filter.

Sure, later today I will post a complete solution which implements totalCount, sorting, and filtering options on connections using the approach described above.

from graphene-sqlalchemy-filter.

Here's a working example similar to content already given above, but with less descriptions and more hands-on code:

1. SQLAlchemy ORM models:

Example shows ModelA having many ModelBs, but type of relationship doesn't matter:

class ModelA(db.Model):
  model_bs = db.relationship('ModelB')
  model_bs_with_filter = db.relationship('ModelB', lazy='dynamic')

class ModelB(db.Model):
  model_a = db.relationship('ModelA')

Field model_bs is a standard SQLAlchemyConnectionField, and can have any type of db.relationship configured on it as required by the application.
If any eager loading is configured on this field, it will be eager-loaded as expected.
This field will appear in GraphQL by default, but if a person uses it, it will be plain SQLAlchemyConnectionField with added totalCount field but with no sort or filters options.

Field model_bs_with_filter (and all connection fields ending in "_with_filter") will be automatically turned into FilterableConnectionFields, and have totalCount, sort and filters options on them. They must have lazy='dynamic'. If lazy is set to any other type, GraphQL options will be there but the data returned will not be sorted or filtered.
Also, since lazy='dynamic' is the only option which is not part of any data loader models, this data will never be eager-loaded.
The totalCount feature will come from the GCountedConnection class shown further below.
The sort option will come from built-in feature present in graphene-sqlalchemy.
The filters option will of course come from Artem's wonderful graphene-sqlalchemy-filter.

2. Necessary definitions which need to exist before we add the GraphQL definitions:

In them, we:

• Override resolve_connection to fix one bug and to extend it to apply sort and filters options to AppenderBaseQuery, which we get for db.relationships defined with lazy='dynamic'
• Implement filter_for() which just returns filter objects based on GraphQL class names. If Meta classes support specifying filters = in the options, then this whole function could be removed and manually specifying filters = would not be needed anywhere. Please confirm if you try this and it works.
• Create a field factory function which creates SQLAlchemyConnectionFields or FilterableConnectionFields, based on whether the field name ends in "_with_filter". Better implementation could decide on type based on lazy= value of db.relationship and not depending on the names of fields.
• Add GCountedConnection class which will provide queryable totalCount field on all connections

import graphene
import graphene_sqlalchemy
from sqlalchemy import and_, or_
from graphene_sqlalchemy import SQLAlchemyConnectionField, SQLAlchemyObjectType
from graphene_sqlalchemy_filter import FilterableConnectionField, FilterSet
from sqlalchemy.orm.query import Query
from graphene_sqlalchemy.fields import UnsortedSQLAlchemyConnectionField
from graphql_relay.connection.arrayconnection import connection_from_list_slice
from graphene.relay.connection import PageInfo

@classmethod
def resolve_connection2(cls, connection_type, model, info, args, resolved):

    # This if() is modified to support connections at root level.
    # It prevents/fixes bug https://github.com/graphql-python/graphene-sqlalchemy/issues/236
    t=type(resolved)
    if resolved is None or t==FilterableConnectionField or t==SQLAlchemyConnectionField:
        resolved = cls.get_query(model, info, **args)
   
    # This section is copied from graphene-sqlalchemy{,-filter}'s get_query() functions
    # Note: why if() doesn't work comparing classes, but only works with string comparison?
    elif str(t)=="<class 'sqlalchemy.orm.dynamic.AppenderBaseQuery'>":
        query=resolved
        sort = args.get('sort')
        if sort is not None:
            if isinstance(sort, six.string_types):
                query = query.order_by(sort.value)
            else:
                query = query.order_by(*(col.value for col in sort))
        request_filters = args.get(cls.filter_arg)
        if request_filters:
            filter_set = cls.get_filter_set(info)
            query = filter_set.filter(info, query, request_filters)
        resolved=query

    # The rest is standard resolve_connection() code, unmodified:
    if isinstance(resolved, Query):
        _len = resolved.count()
    else:
        _len = len(resolved)
    connection = connection_from_list_slice(
        resolved,
        args,
        slice_start=0,
        list_length=_len,
        list_slice_length=_len,
        connection_type=connection_type,
        pageinfo_type=PageInfo,
        edge_type=connection_type.Edge,
    )
    connection.iterable = resolved
    connection.length = _len
    return connection

# And we need to make sure this function is used instead of default:
UnsortedSQLAlchemyConnectionField.resolve_connection = resolve_connection2

# Based on object class, returns the appropriate filter class
def filter_for(model_type):
  if model_type == GModelA:
    return(GModelAFilter())
  elif model_type == GModelB:
    return(GModelBFilter())
  else:
    raise(Exception('Unknown model_type %s; extend filter_for() to solve it' % str(model_type)))

# Creates connection field using FilterableConnectionField rather than the default
def field_factory(relationship, registry, **field_kwargs):
    model = relationship.mapper.entity
    model_type = registry.get_type_for_model(model)
    if str(relationship).endswith("_with_filter"):
      return FilterableConnectionField(model_type._meta.connection, filters=filter_for(model_type), **field_kwargs)
    else:
      # No ._meta.connection here because we don't want the non-functional 'sort' argument to appear in graphql schema
      return SQLAlchemyConnectionField(model_type, **field_kwargs)

class GCountedConnection(graphene.relay.Connection):
  class Meta:
    abstract = True

  total_count = graphene.Field(graphene.NonNull(graphene.Int))
  def resolve_total_count(s,i, **kwargs):
    return(s.length)

3. GraphQL definitions of the two ORM models.

(To avoid class name conflicts between ORM models and GraphQL, I prefer to prefix GraphQL classes with "G", rather than to rename models into e.g. "ModelAModel" or "ModelANode".)

The class of primary importance is GModelB here, because when we query modelA(id: ...) { modelBs { ... } }, this will trigger the GModelB connection.

class GModelAFilter(FilterSet):
  class Meta:
    model = ModelA
    fields = {
      'name': [...],
    }
class GModelA(SQLAlchemyObjectType):
  class Meta:
    model = ModelA
    use_connection = True
    connection_class = GCountedConnection
    connection_field_factory = field_factory

class GModelBFilter(FilterSet):
  class Meta:
    model = ModelB
    fields = {
      'name': [...],
    }
class GModelB(SQLAlchemyObjectType):
  class Meta:
    model = ModelB
    use_connection = True
    connection_class = GCountedConnection
    connection_field_factory = field_factory

4. GraphQL definition for a connection to ModelAs and ModelBs directly from the root of the tree.

This example would work in graphene-sqlalchemy-filter by itself.
Maybe useful addition in this example is that it is using connection classes created automatically by graphene-sqlalchemy. It does so by referencing the ._meta.connection on classes, and so it avoids redundant (and possibly conflicting) manual creation of connection classes.

Also, it is noticeable that we don't have to go through field_factory() here; this is a manually defined connection so we can instantiate FilterableConnectionField directly. The naming ending in "_with_filter" is kept for uniformity with other fields, but is not strictly required here.

(We could potentially also remove the filters = option, if the above-mentioned assumption about Meta: filters = field existing and being used automatically is correct):

class Query(ObjectType):
    model_as_with_filter = FilterableConnectionField(
        GModelA._meta.connection, filters=filter_for(GModelA)
    )
    model_bs_with_filter = FilterableConnectionField(
        ModelB._meta.connection, filters=filter_for(GModelB)
    )

5. Finally, the queries that should automatically become available with the setup as described would be something like:

{
  modelBsWithFilter( sort: ID_ASC, filters: {nameIn: "somename"}) {
    totalCount
    edges { node { ... } } 
  }

  modelA(id: ...) {
    modelBsWithFilter( sort: ID_ASC, filters: {nameIn: "somename"}) {
      totalCount
      edges { node { ... } } 
  }
}

If you find anything missing, let me know. Good luck.

from graphene-sqlalchemy-filter.

Hey,

1. For misspell, yes, the message in that case is correct. It shows that GraphQL knows about the good-named field (you can also check that in the live schema/docs browser)
2. For the first error ('message': 'Unknown argument "filters" on field "modelBsWithFilter" of type "modelA") it would seem as if it's trying to use ModelA connection instead of ModelB? Maybe worth double-checking? Also check what type is reported in the schema/docs browser; it should be ModelB.
3. If the code and autogenerated schema/docs look good to you, then what you can do is, in the field_factory function you can insert some print() statements for both the if and else cases. They will run at application startup, and you'll be able to see what fields got created with which type and arguments.
4. Regarding relationships without foreign keys, yes, I agree this is probably not the cause of the problem. However, it's interesting that I had the same issue (relationships without foreign keys) and couldn't get them to work because the app would give some graphene-sqlalchemy-specific error. In any case, I didn't have uselist etc., so I'll study your example! Thanks.
5. Ah indeed, this was missing from my examples, I will update the query above for archival.

Let me know of results from 1-3.

from graphene-sqlalchemy-filter.

Hi, everyone! New version released. New documentation section here.

Feel free to open a new issue if you have any difficulties with the new functionality ✌

from graphene-sqlalchemy-filter.

Thanks, I see my mistake now, thanks a lot!

from graphene-sqlalchemy-filter.

I was about to ask the exact same question :)

Currently, I've hacked this together:

class TransactionItemFilter(FilterSet):
    class Meta:
        model = orm.TransactionItem
        fields = {
            'some_id': [...]
        }


class TransactionFilter(FilterSet):
    transaction_items = TransactionItemFilter()

    class Meta:
        model = orm.Transaction
        fields = {
            'memo': [...],
            'to_id': [...],
            'from_id': [...],
            'date_created': [...],
            'date_updated': [...],
        }

    @classmethod
    def transaction_items_filter(cls, info, query, value):
        query = query.join(TransactionItem, Transaction.transaction_id == TransactionItem.transaction_id)

        return TransactionItemFilter._translate_many_filter(info, query, value, join_by=and_)

It works, but, obviously, reaching into private methods like that isn't a good idea :)

from graphene-sqlalchemy-filter.

Hey, yes, that's what I meant.

from graphene-sqlalchemy-filter.

Unfortunately, I don't know how to properly implement this right now with graphene-sqlalchemy wrappers. I tried to implement this feature but couldn't get it to work.

Based on the experience of using graphene in production systems, I believe that in most cases it is more correct to use dataloaders than complex ORM queries.

I can try to give an example of using dataloaders with this module.

from graphene-sqlalchemy-filter.

Good job! My attempts had similar implementations. The main task is to avoid the problem of multiple database queries (n+1). As far as I understand, using lazy='dynamic' won't solve this problem, isn't it?

from graphene-sqlalchemy-filter.

I don't know what happens with that. I didn't look into the content of the BaseQuery -- I just assumed that the content in it (which we get when lazy='dynamic') is already correctly constructed by SQLALchemy, and that we only need to update it to add sorting/filtering options before it runs.

I will check the docs and try to see how it works in more details so that I can properly interpret your question.
Thanks!

from graphene-sqlalchemy-filter.

Hey @art1415926535 , yes, you are right that it won't solve N+1 for users who want to filter and also eager-load resources.

Specifically, to clarify this for readers, this N+1 problem means that if someone wants to run a GraphQL query like modelAs { modelBs(filters: ...) {}}, then if model_bs is defined as db.relationship(..., lazy='dynamic'), it will not be possible to eager-load modelA and filtered modelBs at once. SQLAlchemy will first load all modelAs in one query, and then one additional query when each modelB is accessed, so the total amount of SQL queries for all modelAs with their modelBs will be 1 + (N of As), instead of 1 total.
This is because lazy='dynamic' is the only option in SQLAlchemy which is not part of any loader models. However, at the same time, it is also the only one which gives us access to the query before actually running it, so that we can update it with sorting/filtering options.

But, since lazy='dynamic' is treated differently than all other options (it isn't part of any current SQLAlchemy's eager loading models), would it make sense to support lazy='dynamic' first, and then look into the other group of lazy= options (all the eager loaded ones) as a separate question?

from graphene-sqlalchemy-filter.

I went ahead and implemented this for test. It seems to work nicely.
The exact solution I used is as follows:

1. As mentioned, override resolve_connection and in its IFs add a test for when type(resolved) == ... AppenderBaseQuery. This condition is true when the relationship is created with lazy='dynamic'.
   Then since resolved is a query, just apply identical filtering that your code applies inside get_query, followed by applying the identical sort argument that SQLAlchemyConnectionField does in its get_query function.

2. Then, about your remark re. N+1, as well the mentioned fact that turning relationships into lazy='dynamic' makes a difference on python level, requiring one to call obj.rel.all() to get the data rather than just obj.rel.
   I solved this by slightly modifying the field factory function so that it only wraps fields into filtered connections if they end with "_with_filter". If they don't, it creates them as normal SQLAlchemyConnectionField.

3. Finally, in models, where automatic filtered connections are wanted, one can do:

# This remains normal, unmodified relationship/connection
model_bs = db.relationship(ModelB)

# This automatically gets wrapped into filtered connection
model_bs_with_filter = db.relationship(ModelB, lazy='dynamic')

And then in GraphQL, one can call either the normal modelBs which behaves like it always did, or use modelBsWithFilter(filters: ... ) { }.

4. I found this to be a good solution until some even better approach is figured out that works on an even lower level and automatically affects all connections without them having to be lazy='dynamic'.

from graphene-sqlalchemy-filter.

Do I understand correctly that you suggest using model_bs_with_filter with an unsolved n+1 problem, and model_bs in other cases for optimized queries?

from graphene-sqlalchemy-filter.

Yes, this I found to be an acceptable intermediate solution, for me at least.

from graphene-sqlalchemy-filter.

I went ahead and implemented this for test. It seems to work nicely.
The exact solution I used is as follows:

1. As mentioned, override `resolve_connection` and in its IFs add a test for when `type(resolved) == ... AppenderBaseQuery`. This condition is true when the relationship is created with `lazy='dynamic'`.
   Then since `resolved` is a query, just apply identical filtering that your code applies inside `get_query`, followed by applying the identical `sort` argument that `SQLAlchemyConnectionField` does in its `get_query` function.

Hi @docelic really nice work! Would you mind sharing your code about overriding the resolve_connection method? Are you subclassing the FilterableConnectionField?

from graphene-sqlalchemy-filter.

@docelic thanks a lot for your work. Haven't tested yet your idea about not specifying filters manually, as I wanted first to make it working.

At the moment it seems I can only run the query placing the filters argument either in modelA or modelB (basically the first query). The second query gives me

'message': 'Unknown argument "filters" on field "modelBsWithFilter" of type "modelA"

Curiously enough, I noticed that if I misspell (e.g. modelBsWithFilters), then I get

'Cannot query field "modelBsWithFilters" on type "modelA". Did you mean "modelBsWithFilter"?'

I think I'm missing a small detail somewhere.

A noticeable difference is that I defined the models for existing database tables with existing logical relationships, but missing FOREIGN KEY constraints in the table definition. Specifying lazy='dynamic' generates an hidden exception (graphql-python/graphene-sqlalchemy#121) unless I specify a primaryjoin AND uselist=True. The *_with_filter relationship reads:

class ModelA(Base):
    companies_with_filter = relationship(
        "ModelB",
        primaryjoin="foreign(ModelA.model_b) == ModelB.id",
        lazy="dynamic",
        uselist=True,
    )

Nevertheless, I don't think my problem is related to this.

As a side note, when defining the query, the connection filed and the filter_for hook should get as input the SQLAlchemyObjectType, GModelB, in place of the ORM, and the model_a connection is missing:

class Query(ObjectType):
    model_a = FilterableConnectionField(
        GModelA._meta.connection, filters=filter_for(GModelA)
    )
    model_bs_with_filter = FilterableConnectionField(
        ModelB._meta.connection, filters=filter_for(GModelB)
    )

from graphene-sqlalchemy-filter.

Hey @docelic thanks for pointing me in the right direction, as indeed the problem relied in the model definition. Basically what was indeed happening was the field_factory would not receive the model_bs_with_filter relationship, but the model_bs one. I got a working version defining the two relationships in ModelA using backref=__tablename__ instead of back_populates, which would raise

sqlalchemy.exc.ArgumentError: ModelA.model_bs and back-reference ModelB.model_a are both of the same direction symbol('ONETOMANY').  Did you mean to set remote_side on the many-to-one side

, and no relationship at all in ModelB.

I still have some doubts about the intended behavior. I was expecting that the results of the query would be the Intersection of the results, rather than the Union in presence of the filters. Is this what you get as well?

from graphene-sqlalchemy-filter.

Hey, correct, the results of { modelA { modelBs { ... } } are (or should be) an intersection. They are the standard result of modela.model_bs, additionally constrained by filters: { ... }.

If you're getting unexpected results, try adding some print statements to resolve_connection2.
Specifically, in the if where you get the AppenderBaseQuery, print the value of resolved before and after the sorting and filtering options are applied. The value in it is the SQL query that will run.

Then just visually check whether the original query was correct, and whether it is still correct after applying additional rules.

from graphene-sqlalchemy-filter.

Interesting, it looks like that that if is never fulfilled as str(t) is "<class 'sqlalchemy.orm.dynamic.AppenderQuery'>" (not AppenderBaseQuery guess has to do with sqlalchemy version). Now replacing with elif isinstance(resolved, AppenderQuery) does the trick, although the results still gives me the ModelA node together with the nested modelBsWithFilter node, which has null edges. Looks like queries are executed separately without WHERE model_b == (nested model_bs_with_flter query).

Maybe a naive question, where should the intersection between the Query (the parent type) and the AppenderQuery should happen?

from graphene-sqlalchemy-filter.

When resolve_connection gets called, the query we receive in the variable resolved should already be populated with the correct SQL statement that only returns modelBs which belong to modelA.

On top of this already-correct query, the code is supposed to just add the sorting/filtering options.

So, specifically, I believe that when you add print(resolved) on entry into this function or into the if, then the query you see should already have the interesecting conditions (i.e. it should already be set up to retrieve only modelBs belonging to modelA).

And then after you print it the second time (after the sort/filter code), it should be the same query, just additionally updated to limit the results to a subset that satisfies the additional conditions.

from graphene-sqlalchemy-filter.

The first time resolve_collection is called, resolved is populated with the query on ModelA table with its own filter.

SELECT ... FROM model_a                                                                                                                           
WHERE model_a.id IN (%(id_1)s) ORDER BY model_a.id ASC

The other times (one time for each record selected) resolved is populated with the query on ModelB table with its own filter.

SELECT ...   FROM model_bs                                                                                                                             
WHERE %(param_1)s = model_bs.id

with param_1 from the first query, which looks correct.

Nevertheless, even though the second query doesn't return anything (because of the filtering condition), the result of the graphql query returns the records from the first query, together with the nested result of the second query (none for this particular filter)

[OrderedDict([('node',
               OrderedDict([('id', '499'),
                            ('modelBsWithFilter',
                             OrderedDict([('edges', [])]))]))]),
 OrderedDict([('node',
               OrderedDict([('id', '541'),
                            ('modelBsWithFilter',
                             OrderedDict([('edges', [])]))]))])]

, while I'd expect this result to return no records at all, as from the combined query, which I could imagine should look like:

SELECT ... FROM model_a                                                                                                                           
WHERE model_a.id IN (%(id_1)s) AND model_bs.id IN (SELECT ...   FROM model_bs                                                                                                                             
WHERE %(param_1)s = model_bs.id) ORDER BY model_a.id ASC

does it make sense to you? I would expect either the queries to be combined in a single query, or the results being combined afterwards (which I can still do, but I could do the same defining custom resolvers in Query anyway and without using filters)

from graphene-sqlalchemy-filter.

Ah I understand what you mean... but I think the behavior you are seeing is normal graphene-sqlalchemy behavior.
It works in this way even if you do normal connection queries without graphene-sqlalchemy-filter.

Suppose that you have standard graphene-sqlalchemy setup and you query:

query {
  modelAs {
    edges {
      node {
        modelBs {
          edges { node { id } }
        }
     }
  }
}

Even if there was no data for modelBs anywhere, this query would still give you all modelAs, and then the modelBs lists would just be empty arrays, wouldn't they?

from graphene-sqlalchemy-filter.

By the way, just a comment related to your previous question, and not to the main content of this ticket:

If you really want the behavior you described, I believe you would need to create a separate field for this, or at least use some (possibly custom) filters.

For example, suppose that you only wanted to show those modelAs which have at least one modelB.

To do this you'd either need to define new field called e.g. modelAsWithAtLeastOneModelB, and add appropriate db.relationship options to it, so that it only queries what you want.

Or you could/would create a field on modelA called modelBCount, which returns number of modelBs. Then you would query this like:

modelAs( filters: { modelBCountGt: 0 } ) {
  ...
}

This would give you only those modelAs for which modelBCount is greater than 0.

from graphene-sqlalchemy-filter.

Hey @art1415926535 cool! I'm trying that out just now, and it's not clear from the README if the SQLAlchemy-model setup for the relationship is not properly configured, basically the nested field users on the allGroups connection in your example. I'm getting 'Unknown argument "filters" on field "<relationship>" of type "<sqlalchemyobjecttype>", where the is the one previously (before this release) declared with *_with_filter to allow the field_for() hook to return filter objects based on GraphQL class names.

from graphene-sqlalchemy-filter.

@babaMar send your models or see this example.

from graphene-sqlalchemy-filter.

My main problem is that the DB structure already exists and I can't define foreign keys in MySQL. So I'm specifying the relationship with the primary join like so:

class Clients(Base):
    __tablename__ = "clients"

    id = Column(INTEGER(11), primary_key=True)
    name = Column(Text, nullable=False, index=True)


class Transactions(Base):
    __abstract__ = True

    id = Column(INTEGER(11), primary_key=True)


class Records(Transactions):
    __tablename__ = "records"

    client = Column(INTEGER(11), nullable=False, index=True)

    clients = relationship(
        "Clients",
        lazy="bulk",
        primaryjoin=foreign(client) == Clients.id,
        backref=backref("lookup_id", lazy="bulk"),
    )

from graphene-sqlalchemy-filter.

See new example. I changed the Records model a bit.

Request example:

{
  allClients(filters: {nameIlike: "%a%"}) {
    edges {
      node {
        id
        name
        records(filters: {idGt: 2}) {
          edges {
            node {
              id
            }
          }
        }
      }
    }
  }
}

from graphene-sqlalchemy-filter.

I was experimenting with this today and I noticed that it seems like the filters argument is only available on subfields of other types that implement relay.Node as an interface. In my case I have some types which are simple (don't use relay style pagination), but contain subfileds that are connection objects. In those cases, the connection object has no filter arguments defined when I introspect the schema. Is this expected?

Here's a barebones description of my schema:
models.py

class User(db.Model):
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.String(225), unique=True)
    posts = relationship("Post", back_populates="user")

class Project(db.Model):
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.String(225), unique=True)
    user = relationship("User", back_populates="posts")

filters.py

class PostFilter(FilterSet):
    class Meta:
        model = Post
        fields = {
            'name': [...]
        }

class CustomFilter(FilterableConnectionField):
    filters = {
        Post: PostFilter()
    }

schema.py

class CountableConnection(graphene.Connection):
    class Meta:
        abstract = True

    total_count = graphene.Int()
    edge_count = graphene.Int()

    @staticmethod
    def resolve_total_count(root, info):
        return root.length

    @staticmethod
    def resolve_edge_count(root, info):
        return len(root.edges)

class User(SQLAlchemyObjectType):
    class Meta:
        model = UserModel


class Post(SQLAlchemyObjectType):
    class Meta:
        model = PostModel
        interfaces = (Node,)
        connection_field_factory = CustomFilter.factory
        connection_class = CountableConnection

With these definitions I am not able to run:

query {
    allUsers {
        posts (filters: { nameLike: "test"}) {
            totalCount
        }
    }
}

Raising: "message": "Unknown argument \"filters\" on field \"posts\" of type \"User\".",

from graphene-sqlalchemy-filter.

Try this:

class User(SQLAlchemyObjectType):
    class Meta:
        model = UserModel
        connection_field_factory = CustomFilter.factory

In graphene-sqlalchemy module allUsers creates posts field. You are not giving User connection_field_factory which is needed to search posts filters.

from graphene-sqlalchemy-filter.

That worked, thanks!

from graphene-sqlalchemy-filter.