How to tweak query structure from relationships

[shlomi84]

I'm working on a simple CRUD REST API to learn GraphQL & SqlAlchemy. I have a Movie table

class Movie(Base, Serializer):
    __tablename__ = 'movie'

    id = Column(Integer, primary_key=True, index=True)
    movie = Column(String(50), nullable=False, unique=True)
    budget = Column(Float, nullable=False)
    genre_id = Column(Integer, ForeignKey('genre.id'), nullable=False)
    rating = Column(Float, nullable=False)
    studio_id = Column(Integer, ForeignKey('studio.id'), nullable=False)
    director_id = Column(Integer, ForeignKey('director.id'), nullable=False)
    director = relationship(
        Director,
        backref=backref('movies', uselist=True, cascade='delete,all')
    )

    genre = relationship(
        Genre,
        backref=backref('movies', uselist=True, cascade='delete,all')
    )

    studio = relationship(
        Studio,
        backref=backref('movies', uselist=True, cascade='delete,all')
    )

    actors = relationship(
        Actor,
        secondary=movie_actor_association_table,
        backref='movies',
        uselist=True
    )

that has its own properties (movie, budget, rating) but also 4 foreign keys (genre, studio, director, actors). my GraphQL types are simple

class Movie(SQLAlchemyObjectType):
    class Meta:
        model = MovieModel
        interfaces = (relay.Node,)

class Director(SQLAlchemyObjectType):
    class Meta:
        model = DirectorModel
        interfaces = (relay.Node,)

class Genre(SQLAlchemyObjectType):
    class Meta:
        model = GenreModel
        interfaces = (relay.Node,)

class Studio(SQLAlchemyObjectType):
    class Meta:
        model = StudioModel
        interfaces = (relay.Node,)

class Actor(SQLAlchemyObjectType):
    class Meta:
        model = ActorModel
        interfaces = (relay.Node,)

however, now when I query data, for the relationship tables, I have to replicate key, value pairs to get simple data

movies {
    edges {
      node {
        id
        movie
        budget
        genre {
          genre
        }
        rating
        studio {
          studio
        }
        director {
          director
        }
        actors {
          edges {
            node{
              actor
            }
          }
        }
      }
    }
  }

i.e. can I avoid using genre {genre}, studio {studio}, etc. and just retrieve genre directly inside the movie?

bonus question: adding filters to these relationships doesn't work I have a movie filter

class MovieFilter(FilterSet):
    class Meta:
        model = MovieModel
        fields = {
            'id': ['eq'],
            'movie': ['eq', 'ilike'],
            'rating': ['eq', 'gt', 'gte']
        }

that I can use like so

class Query(graphene.ObjectType):
    node = relay.Node.Field()
    movies = FilterableConnectionField(Movie.connection, filters=MovieFilter())

to have filtering available for my movie table. However, the filters only work for the fields defined in the movie table itself, i.e. movie name, rating, budget. Does anyone know how I can use graphene-sqlalchemy-filter to filter for all fields (director/actor/genre/studio)? It seems to me that GraphQL doesn't handle relationships all that well.

[erikwrede]

The simplest way to achieve this would be to add properties to the SQLAlchemy Movie model:

@property
def genre_name(self):
  return self.genre.genre

This property will become available in your schema.

If your Genre entity only consists of a single field genre, it would make sense to remove the genre Type and Model from your schema and implement the entity by adding the genre field to all models having a relationship with the Genre entity. However, this is a question of schema design & normalization.

Regarding your other question about the filtering library, you will need to implement custom filtering to be able to filter over relationships. Consider the is_admin_filter from the library's readme as an example.

[github-actions[bot]]

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.