Filter Error When DB column is named `id` but aliased to something else

[palisadoes]

Our database was created prior to knowing about GraphQL. We named many of our primary key table columns id. To get around this issue we renamed all the id columns to id_ for GraphQL to work as expected.

Here is an example:

    id_ = Column(
        'id', BIGINT(20, unsigned=True), primary_key=True, unique=True)

• The graphene-sqlalchemy-filter package works as described for the Quick start cases in README.md using this strategy
• but we started to get an unusual error when we attempted to do Filter registration and nested fields filters described in the README.md file.

We wanted to use this latter method to take advantage of dataloading performance improvements.

This issue only occurs when backrefs are used using ForeignKeys that are table.id where we reference the column in python as id_. You can see this in the iGraphQL error, where the very first lookup works, until it hits the cityCustomer backref, where the error is triggered and the rest of the query results do not get returned.

I'd like to know if this is a valid error, or whether we have a misconfiguration.

Packages

Flask-GraphQL==2.0.1
graphene==2.1.9
graphene-sqlalchemy==2.3.0
graphene-sqlalchemy-filter==1.13.0
graphql-core==2.3.2
graphql-relay==2.0.1
graphql-server==3.0.0b5
graphql-server-core==1.2.0
SQLAlchemy==1.4.31

Code

class CityAttribute():
    """Descriptive attributes of the City table.

    A generic class to mutualize description of attributes for both queries
    and mutations.

    """
    id_ = graphene.Int(description='City ID')
    name = graphene.String(description='City Name')
    customer_id = graphene.Int(description='Customer ID (Foreign Key)')


class CustomerAttribute():
    """Descriptive attributes of the Customer table.

    A generic class to mutualize description of attributes for both queries
    and mutations.

    """

    id_ = graphene.Int(description='Customer ID')
    name = graphene.String(description='Customer Name')

    
class CustomerModel(BASE):
    """Database table definition."""

    __tablename__ = 'sy_customers'

    id_ = Column(
        'id', BIGINT(20, unsigned=True), primary_key=True, unique=True)
    name = Column(String(128))

    
class CityModel(BASE):
    """Database table definition."""

    __tablename__ = 'sy_citys'

    id_ = Column(
        'id', BIGINT(20, unsigned=True), primary_key=True, unique=True)
    name = Column(String(128))
    customer_id = Column(
        ForeignKey('sy_customers.id'),
        nullable=False, index=True, server_default=text('1'))

    # Uses cascade='delete,all' to propagate the deletion of an entry
    city_to_customer = relationship(
        Customer,
        backref=backref(
            'city_customer', uselist=True, cascade='delete,all'))

            
class CityFilter(FilterSet):
    """City custom filter."""

    class Meta:
        """Define the metadata."""

        model = CityModel
        fields = {
            'id_': _NUMERIC,
            'name': _STRING,
            'customer_id': _NUMERIC,
        }


class CustomerFilter(FilterSet):
    """Customer custom filter."""

    class Meta:
        """Define the metadata."""

        model = CustomerModel
        fields = {
            'id_': _NUMERIC,
            'name': _STRING,
        }

        
class CustomField(FilterableConnectionField):
    """Define node level filters."""

    filters = {
        CityModel: CityFilter(),
        CustomerModel: CustomerFilter(),
    }


class CityNode(SQLAlchemyObjectType, CityAttribute):
    """City node."""

    class Meta:
        """Define the metadata."""

        model = CityModel
        interfaces = (graphene.relay.Node,)
        connection_field_factory = CustomField.factory


class CustomerNode(SQLAlchemyObjectType, CustomerAttribute):
    """Customer node."""

    class Meta:
        """Define the metadata."""

        model = CustomerModel
        interfaces = (graphene.relay.Node,)
        connection_field_factory = CustomField.factory


class CityConnect(relay.Connection):
    """City node."""

    class Meta:
        """Define the metadata."""
        node = CityNode


class CustomerConnect(Connection):
    """Customer node."""

    class Meta:
        """Define the metadata."""
        node = CustomerNode


class Query(graphene.ObjectType):
    """Define GraphQL queries."""

    node = relay.Node.Field()

    city = graphene.relay.Node.Field(CityNode)
    customer = graphene.relay.Node.Field(CustomerNode)
    

    all_city = CustomField(CityConnect, sort=None)
    all_customer = CustomField(CustomerConnect, sort=None)

    
# Make the schema global
SCHEMA = graphene.Schema(query=Query)

GraphQL Query

{
  allCustomer(filters: {id_: 5}) {
    edges {
      node {
        name
        id_
        cityCustomer {
          edges {
            node {
              id_
              name
            }
          }
        }
      }
    }
  }
}

iGraphQL error

{
  "errors": [
    {
      "message": "type object 'CityModel' has no attribute 'id'",
      "locations": [
        {
          "line": 10,
          "column": 9
        }
      ],
      "path": [
        "allCustomer",
        "edges",
        0,
        "node",
        "cityCustomer"
      ]
    }
  ],
  "data": {
    "allCustomer": {
      "edges": [
        {
          "node": {
            "name": "XXX",
            "id_": 5,
            "cityCustomer": null
          }
        }
      ]
    }
  }
}

Trace

ERROR 2022-02-24 18:49:53,648 - Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/promise/dataloader.py", line 261, in dispatch_queue_batch
    batch_promise = loader.batch_load_fn(keys)
  File "/usr/local/lib/python3.6/site-packages/graphene_sqlalchemy_filter/connection_field.py", line 168, in batch_load_fn
    query: 'Query' = self._get_query().filter(
  File "/usr/local/lib/python3.6/site-packages/graphene_sqlalchemy_filter/connection_field.py", line 263, in _get_query
    query, self.graphql_args.get('sort'), aliased_model
  File "/usr/local/lib/python3.6/site-packages/graphene_sqlalchemy_filter/connection_field.py", line 276, in _sorted_query
    sort_field = getattr(by_model, sort_field_name)
  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/orm/util.py", line 569, in __getattr__
    attr = getattr(target, key)
graphql.error.located_error.GraphQLLocatedError: type object 'CityModel' has no attribute 'id'

[masterchef]

I reported a similar issue in graphql-python https://github.com/graphql-python/graphene-sqlalchemy/issues/392

Still looking for resolution.

[palisadoes]

This should have been fixed in this PR. Beware that the syntax for filtering has changed to match that used by Dgraph.

• https://github.com/graphql-python/graphene-sqlalchemy/pull/357

I've been using it without issue.