how to Join with get_query

[ssomnoremac]

Just trying to filter on a field from a foreign table.

This doesn't seem to work

def resolve_articles(self, args, context, info):
        query = Article.get_query(context)
        authorName = args.get('authorName')
        return query.join(Author).filter(Author.name == authorName)

[earlgreyz]

You could define a relation in SQLAlchemy model and remove resolver function to make it work

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer(), primary_key=True)
    name = Column(String(64))


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer(), primary_key=True)
    title = Column(String(64))
    author_name = Column(String(), ForeignKey('author.name'))
    author = relationship(
        'Author', 
        primaryjoin=(author_name == foreign(Author.name),
        uselist=False, backref='articles'
    )

[dos-me]

How can I make something like that

class Article(SQLAlchemyObjectType):

    author_name = graphene.Field(graphene.String)

    class Meta:
        model = ArticleModel
        interfaces = (relay.Node,)


class ArticleConnectionField(SQLAlchemyConnectionField):

    @classmethod
    def get_query(cls, model, info, **kwargs):
        query = super(ArticleConnectionField, cls).get_query(model, info, **kwargs)

        query = query.join(Author).add_colums(
            Author.name.label("author_name")
        )

        # Exception: Received incompatible instance "(<Article id:15776>, 'Author Name')"

        return query


class MainQuery(graphene.ObjectType):

    list_articles = ArticleConnectionField(Article)

Now I get an error Received incompatible instance

[earlgreyz]

Hey sorry for delay, but it's been a while since I used sqlalchemy so needed a quick refresh. You don't need to subclass a connection field instead use a standard one with a custom resolver.

class Author(SQLAlchemyObjectType):
    class Meta:
        model = models.Author
        interfaces = [relay.Node]


class Article(SQLAlchemyObjectType):
    class Meta:
        model = models.Article
        interfaces = [relay.Node]


class Query(graphene.ObjectType):
    all_articles = SQLAlchemyConnectionField(Article, author_name=graphene.String())

    def resolve_all_articles(self, info, author_name):
        query = Article.get_query(info)
        return query.join(models.Author).filter(models.Author.name == author_name).all()

You can file complete files I've used to test here:
https://gist.github.com/earlgreyz/bcbb9dc1b1e0618fe96125cd57b563a0

[dos-me]

Hey. thanks for the reply. I need to explicitly specify the required fields and I don't use relationship only ForeignKey
Adding add_columns to the query gets the error as above. I have a lot of fields that need to be associated with many tables if the field is requested.

    if 'authorName' in requested_fields:
        query = query.add_columns(Author.name.label('author_name'))

I wanted to make such request

[earlgreyz]

I think it is a sqlalchemy error and not the graphene itself. Maybe you should define relations and leave the logic of selecting the columns to graphql-sqlalchemy instead of manually adding them. If you don't want the unnecessary joins you can set the relation lazy=True which will not join unless required.

[earlgreyz]

@zhanibekshynarbek the problem is that when using add_columns you get a tuple with ArticleModel object and an author_name string. I came up with a rather dirty solution but you may be able to make something nice out of this:

class Article(SQLAlchemyObjectType):
    author_name = graphene.String()

    class Meta:
        model = models.Article
        interfaces = [relay.Node]


class Query(graphene.ObjectType):
    all_articles = SQLAlchemyConnectionField(Article)

    def resolve_all_articles(self, info):
        query = Article.get_query(info)
        query = query.join(models.Author).add_columns(
            models.Author.name.label('author_name'))

        def author_name_modifier(article, author_name):
            article.author_name = author_name
            return article

        return [author_name_modifier(a, n) for (a, n) in query.all()]

[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.