How do I return distinct values with graphene_sqlalchemy?

Question

I'm trying to return distinct values from a GraphQL query.

from graphene_sqlalchemy import SQLAlchemyConnectionField
import graphene
from database.model import MyModel


class Query(graphene.ObjectType):
    """Query objects for GraphQL API."""
    node = graphene.relay.Node.Field()
    distinct_values = graphene.List(graphene.String, search=graphene.String())

    def resolve_distinct_values(self, info, search=None, **kwargs):
        return MyModel.field.distinct()

schema = graphene.Schema(query=Query)

The response I receive from my distinctValues query is:

    {
      "message": "User Error: expected iterable, but did not find one for field Query.distinctValues."
    }

There's got to be something simple I'm overlooking here. What is distinct() intended to do?

Answer 1

SQLAlchemy's distinct method is used to add a DISTINCT clause to your query. DISTINCT will remove all duplicate rows.

If you're using PostgreSQL, you can also use the optional parameter, distinct(*expr), to render a DISTINCT ON (<expressions>) clause. This will keep only the "first row" of each set of rows where the given expressions (*expr) evaluate to equal. You'll need to use SQLAlchemy's order_by method to ensure that the desired row appears first.

SQLAlchemy's Query.distinct(*expr) method.

PostgreSQL's DISTINCT Clause.

This example shows a game in which players can travel to different islands, a visit is recorded each time a player arrives at an island. We can use the lastVisit field to show the most recent player to have visited each island.

# models.py
import enum
from sqlalchemy import Column, DateTime, Enum, ForeignKey, Integer, String, orm, sql

class Player(Base):
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    visits = orm.relationship("Visit", backref="visitor")

class Island(enum.Enum):
    Amity = "amity"
    IslaNublar = "islanublar"
    Skull = "skull"
    Treasure = "treasure"

class Visit(Base):
    id = Column(Integer, primary_key=True)
    island = Column(
        Enum(Island, name="island", values_callable=lambda x: [e.value for e in x]),
        nullable=False,
    )
    arrived_at = Column(DateTime(timezone=False), nullable=False, server_default=sql.func.now(), index=True)
    player_id = Column(ForeignKey("players.id"), nullable=False)

# schema.py
from sqlalchemy import orm
from models import Player as PlayerModel, Visit as VisitModel

class Player(SQLAlchemyObjectType):
    class Meta:
        model = PlayerModel
        interfaces = (relay.Node,)

class Visit(SQLAlchemyObjectType):
    class Meta:
        model = VisitModel
        interfaces = (relay.Node,)

class Query(graphene.ObjectType):
    node = relay.Node.Field()

    last_visit = SQLAlchemyConnectionField(Visit.connection, sort=None)

    def resolve_last_visit(self, info):
        return (
            VisitModel.query.options(orm.joinedload(VisitModel.visitor))
            .distinct(VisitModel.island)
            .order_by(VisitModel.island, VisitModel.arrived_at.desc())
            .all()
        )


schema = graphene.Schema(query=Query)

This query:

{
  lastVisit {
    edges {
      node {
        island
        visitor {
          username
        }
      }
    }
  }
}

Would produce this result:

{
  "data": {
    "lastVisit": {
      "edges": [
        {
          "node": {
            "island": "AMITY",
            "visitor": {
              "username": "playertwo"
            }
          }
        },
        {
          "node": {
            "island": "ISLA_NUBLAR",
            "visitor": {
              "username": "playerthree"
            }
          }
        },
        {
          "node": {
            "island": "SKULL",
            "visitor": {
              "username": "playerone"
            }
          }
        },
        {
          "node": {
            "island": "TREASURE",
            "visitor": {
              "username": "playerthree"
            }
          }
        }
      ]
    }
  }
}