Unable to build a query with relationships

[renebefort]

Hello!

I am brand new to the topic and am currently programming a proof-of-concept. I have a problem when I connect two database tables via relations. Unfortunately I get no results from the sub-table in the query and a message in the trace.

My use-case
I have a table where orders are in it (AUFKOPF). In this table exists a column that references to another table. In this table are the order types (AUFTYPEN). The sub-table has a key. In the order table a column is referenced to the key.

AUFKOPF.AUFTYP ---- [N:1] ----> AUFTYPEN.AUFTRAGSTYP (STRING)

Attached is my setup

I use sqlalchemy for the connection to my database. My model definition looks like

models.py

from app import db
from sqlalchemy.orm import relationship, backref
from sqlalchemy import ForeignKey

class Order(db.Model):
    __tablename__ = 'AUFKOPF'
    order_id = db.Column('AUFNR', db.Integer, primary_key=True)
    order_commission = db.Column('KOMMISSION', db.String(40))
    order_type = db.Column('AUFTYP', db.String)
    order_type_ref = relationship("OrderType")
    def to_dict(self):
        return {
            "order_id": self.order_id,
            "order_type": self.order_type,
            "order_commission": self.order_commission
        }

class OrderType(db.Model):
    __tablename__ = 'AUFTYPEN'
    order_type_id = db.Column('AUFTRAGSTYP', db.String, primary_key=True)
    designation = db.Column('BEZEICHNUNG', db.String)
    order_id = db.Column(db.Integer, ForeignKey('AUFKOPF.AUFTYP'))

My resolver looks like:

queries.py

from .models import Kunde, Order
from ariadne import convert_kwargs_to_snake_case

def listOrders_resvoler(obj,info):
    try:
        order = [order.to_dict() for order in Order.query.all()]
        print(order)
        payload = {
            "success": True,
            "order": order
        }
    except Exception as error:
        payload = {
            "success": False,
            "errors": [str(error)]
        }
    return payload

My schema definition looks like:

schema.graphql

schema {
    query: Query
}

type Order {
    order_id: ID!
    order_type: String!
    order_type_ref: [OrderTypeResult]
    order_commission: String!
}

type OrderResult {
    success: Boolean!
    errors: [String]
    order: Order
}

type OrdersResult {
    success: Boolean!
    errors: [String]
    order: [Order]
}

type OrderType {
    order_type_id: ID!
    designation: String
}

type OrderTypeResult {
    success: Boolean!
    errors: [String]
    order: OrderType
}

type OrderTypesResult {
    success: Boolean!
    errors: [String]
    order: [OrderType]
}


type Query {
    listOrders: OrdersResult!
}

The query for getting Orders work very well. But I did not get any data from OrderType.

I can see the following message at the output from flask server, when I reload my client (https://studio.apollographql.com)

GraphQL request:2:9
1 | query IntrospectionQuery {
2 |         _service {
  |         ^
3 |             sdl
graphql.error.graphql_error.GraphQLError: Cannot query field '_service' on type 'Query'.

GraphQL request:2:9
1 | query IntrospectionQuery {
2 |         _service {
  |         ^
3 |             sdl

When I run the following query:

query Order {
  listOrders {
    order {
      order_id
      order_type
      order_type_ref {
        order {
          order_type_id
          designation
        }
        errors
        success
      }
    }
    errors
  }
}

I get the result:

{
  "data": {
    "listOrders": {
      "errors": null,
      "order": [
        {
          "order_id": "86.000000",
          "order_type": "T",
          "order_type_ref": null
        },
        {
          "order_id": "87.000000",
          "order_type": "A",
          "order_type_ref": null
        },
....

Always I get order_type_ref:NULL without any error message.

I hope someone can give me a type what I am doing wrong. I suspect it is not loading the sub table. But I do not know why.

Thanks a lot, René

[rafalp]

Error about _service comes from Apollo's API explorer that sees if your server supports Apollo Federations. It is safe to ignore.

You get null for order_type_ref because its of type relationship and GraphQL's default resolver has no idea how to handle that.

You will need to specify custom resolver for this field that converts this value into iterable.

Albo be careful about relationships in GraphQL or you will run into infamous N+1 problem that GraphQL has, where you will have extra database queries for every item turned in result set.

[renebefort]

Thanks for your feedback.

I think I understand what you mean. I still have no idea how the revolver must look.

I had written a revolver analog for the class Order. This gives me only the objects of the class OrderType. But how is the link to the class order? I have not understood that yet?

Do you have an example for me?

[rafalp]

I guess resolver should just do return list(order.order_type_ref.all())?