how to handle recursive queries?

[Tryph]

Hello,

I am trying to handle recursive query with graphene-sqlachemy using a postgres database and cannot find a way to achieve what i want.

I have the following model:

# models module
class Location(Base):
    __tablename__ = 'location'
    id = Column(BigInteger, primary_key=True, nullable=False)
    name = Column(String(200), nullable=False)
    parent_id = Column(BigInteger, ForeignKey('location.id'))
    name_vector = Column(TSVECTOR)
    parent = relationship('Location', remote_side=[id], backref=backref(
        'children', uselist=True, cascade=False))

As you can see, this model references itself, and represents a tree of locations. It is mapped by the following graphene type:

# schema module
class Location(SQLAlchemyObjectType):
    class Meta:
        model = models.Location
        interfaces = (graphene.relay.Node, )
        only_fields = ('id', 'name', 'parent')

    genealogy = SortableSQLAlchemyConnectionField(lambda: Location)

    def resolve_genealogy(self, info):
        session = info.context['session']
        child_location = (
            session.query(models.Location)
            .filter(models.Location.name == self.name)
            .cte(name='child_location', recursive=True))
        child_alias = child_location.alias('child')
        child_location = child_location.union_all(
            session.query(models.Location)
            .filter(models.Location.id == child_alias.c.parent_id)
        )
        return session.query(child_location)

The genealogy attribute is intended to give access to a list of all parents of a location. To achieve this, the resolve_genealogy function builds a recursive select query and returns it.
The recursive select returns good results but in the form of models.Location instances, not Location instances.

This causes the following error Exception: Received incompatible instance "(1, 'Parc Hélitas', 0, "'helit':2 'parc':1")". because the result type is not mapped...

Below is the traceback:

Traceback (most recent call last):
  File "/home/tryph/PycharmProjects/street-show/venv/lib/python3.5/site-packages/graphql/execution/executor.py", line 330, in complete_value_catching_error
    exe_context, return_type, field_asts, info, result)
  File "/home/tryph/PycharmProjects/street-show/venv/lib/python3.5/site-packages/graphql/execution/executor.py", line 405, in complete_value
    return complete_object_value(exe_context, return_type, field_asts, info, result)
  File "/home/tryph/PycharmProjects/street-show/venv/lib/python3.5/site-packages/graphql/execution/executor.py", line 495, in complete_object_value
    if return_type.is_type_of and not return_type.is_type_of(result, info):
  File "/home/tryph/PycharmProjects/street-show/venv/lib/python3.5/site-packages/graphene_sqlalchemy/types.py", line 143, in is_type_of
    ).format(root))

Is there a way to map model instances to SQLAlchemyObjectType ones?
Is there a better way to handle recursive queries?
Am I doing it totally wrong? Is there a proper way to get a list of parents recursively?

[Tryph]

Yo!

I just managed to solve my problem with a solution that satisfies me for the moment (and because I have no better idea...). It consists in extracting a list of ids from the recursive select and using it to filter the Location objects to return.
Basically, I modified the resolve_genealogy function as follows:

def resolve_genealogy(self, info):
        session = info.context['session']
        child_location = (
            session.query(models.Location)
            .filter(models.Location.name == self.name)
            .cte(name='child_location', recursive=True))
        child_alias = child_location.alias('child')
        child_location = child_location.union_all(
            session.query(models.Location)
            .filter(models.Location.id == child_alias.c.parent_id)
        )
        genealogy_ids = select([child_location.c.id])
        # since the returned query is built from the graphene object type, it returns the correct type
        return (
            Location.get_query(info)
            .filter(models.Location.id.in_(genealogy_ids))
        )

Not sure it is the best or more efficient solution but it works...

[MrEbbinghaus]

Recursive queries are not intended by GraphQL.

Here it is described why:
graphql/graphql-spec#91 (comment)

[erikwrede]

Unless GQL Spec changes, I don't see a case for adding support for recursive queries. The alternative, for now, is to use the workaround by @Tryph or change the schema design.

[github-actions]

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related topics referencing this issue.