Question — Is this the right way for adding a field that depend on a relationship?

[cglacet]

I have a naive question but I don't know the words to search for. I'm sure this has been asked one million times but I'm clueless.

I have some blog posts with likes on them and I would like to query a list of blog posts with a flag on each post to tell wether the given user liked the post or not.

My current solution:

• I added a field in my schema (under schema.Blog.has_user_liked)
• I added lazy="dynamic" on models.Blog.likes so I can filter likes for a particular user

I wonder if this is the right approach as it makes "a lot" of disjoint SQL requests (at least one to get the blog posts and one per blog post). It sounds like a lot to me, but as I understand there is a compromise here because this will allow to only retrieve some blog posts (not ALL) and still have decent performances since it will not join very large tables. Note that I never plan on using this has_user_liked field anywhere else but in a web view with pagination.

Is this the "right" solution? Is there a better way to achieve this? I would really appreciate any comment/critic about this.

Details about this solution

My model looks like this:

class User(database.Base):
    user_id = IDColumn()

class Blog(database.Base):
    blog_id = IDColumn()
    likes = relationship("Like", back_populates="blog", lazy="dynamic")

class Like(database.Base):
    blog_id = IDColumn(ForeignKey(Blog.blog_id))
    user_id = IDColumn(ForeignKey(User.user_id))
    blog = relationship("Blog", back_populates="likes")

My schema looks like this:

class User(SQLAlchemyObjectType):
    class Meta:
        model = models.User
        interfaces = (Node,)

class Blog(SQLAlchemyObjectType):
    class Meta:
        model = models.Blog
        interfaces = (Node,)

    has_user_liked = graphene.Field(
        graphene.Boolean, user_id=graphene.String(required=True)
    )

    def resolve_has_user_liked(self: models.Blog, info, user_id):
        return self.likes.filter(user_id == user_id).count()

class Like(SQLAlchemyObjectType):
    class Meta:
        model = models.Like
        interfaces = (Node,)


class Query(graphene.ObjectType):
    node = Node.Field()
    blogs = SQLAlchemyConnectionField(Blog)

If I make a request like this one:

query Test($userId: String!) {
    blogs{
        edges {
            node {
                id
                hasUserLiked(userId: $userId)
            }
        }
    }
}

I get the expected result (in my test DB I have 3 posts, one of which has one like from the given user):

{
    "data": {
        "blogs": {
            "edges": [
                {
                    "node": {
                        "id": "Blog:fdb231cd-2a19-4d1f-9443-5717f44275af",
                        "hasUserLiked": false
                    }
                },
                {
                    "node": {
                        "id": "Blog:375afe8e-a52a-4098-98b7-f04a4b504097",
                        "hasUserLiked": false
                    }
                },
                {
                    "node": {
                        "id": "Blog:01580847-bb33-40ef-93e4-8025a1e31145",
                        "hasUserLiked": true
                    }
                }
            ]
        }
    }
}

The "problem" or rather my question concerns the generated SQL queries, from what I can see in SQLAlchemy's log, here is what the requests are:

SELECT count(*) AS count_1 
FROM (SELECT "user".blog.blog_id AS user_blog_blog_id, "user".blog.author AS user_blog_author, "user".blog.date AS user_blog_date, "user".blog.title AS user_blog_title, "user".blog.content AS user_blog_content, "user".blog.image AS user_blog_image, "user".blog.avatar AS user_blog_avatar 
FROM "user".blog) AS anon_1

SELECT count(*) AS count_1 
FROM (SELECT "user".blog.blog_id AS user_blog_blog_id, "user".blog.author AS user_blog_author, "user".blog.date AS user_blog_date, "user".blog.title AS user_blog_title, "user".blog.content AS user_blog_content, "user".blog.image AS user_blog_image, "user".blog.avatar AS user_blog_avatar 
FROM "user".blog) AS anon_1

SELECT "user".blog.blog_id AS user_blog_blog_id, "user".blog.author AS user_blog_author, "user".blog.date AS user_blog_date, "user".blog.title AS user_blog_title, "user".blog.content AS user_blog_content, "user".blog.image AS user_blog_image, "user".blog.avatar AS user_blog_avatar 
FROM "user".blog 
 LIMIT %(param_1)s

SELECT "user".blog.blog_id AS user_blog_blog_id, "user".blog.author AS user_blog_author, "user".blog.date AS user_blog_date, "user".blog.title AS user_blog_title, "user".blog.content AS user_blog_content, "user".blog.image AS user_blog_image, "user".blog.avatar AS user_blog_avatar 
FROM "user".blog 
 LIMIT %(param_1)s

-- User's likes

SELECT count(*) AS count_1 
FROM (SELECT "user"."like".like_id AS user_like_like_id, "user"."like".blog_id AS user_like_blog_id, "user"."like".user_id AS user_like_user_id 
FROM "user"."like" 
WHERE %(param_1)s = "user"."like".blog_id) AS anon_1

SELECT count(*) AS count_1 
FROM (SELECT "user"."like".like_id AS user_like_like_id, "user"."like".blog_id AS user_like_blog_id, "user"."like".user_id AS user_like_user_id 
FROM "user"."like" 
WHERE %(param_1)s = "user"."like".blog_id) AS anon_1

-- {'param_1': UUID('fdb231cd-2a19-4d1f-9443-5717f44275af')}

SELECT count(*) AS count_1 
FROM (SELECT "user"."like".like_id AS user_like_like_id, "user"."like".blog_id AS user_like_blog_id, "user"."like".user_id AS user_like_user_id 
FROM "user"."like" 
WHERE %(param_1)s = "user"."like".blog_id) AS anon_1

SELECT count(*) AS count_1 
FROM (SELECT "user"."like".like_id AS user_like_like_id, "user"."like".blog_id AS user_like_blog_id, "user"."like".user_id AS user_like_user_id 
FROM "user"."like" 
WHERE %(param_1)s = "user"."like".blog_id) AS anon_1

-- {'param_1': UUID('375afe8e-a52a-4098-98b7-f04a4b504097')}

SELECT count(*) AS count_1 
FROM (SELECT "user"."like".like_id AS user_like_like_id, "user"."like".blog_id AS user_like_blog_id, "user"."like".user_id AS user_like_user_id 
FROM "user"."like" 
WHERE %(param_1)s = "user"."like".blog_id) AS anon_1

SELECT count(*) AS count_1 
FROM (SELECT "user"."like".like_id AS user_like_like_id, "user"."like".blog_id AS user_like_blog_id, "user"."like".user_id AS user_like_user_id 
FROM "user"."like" 
WHERE %(param_1)s = "user"."like".blog_id) AS anon_1

-- {'param_1': UUID('01580847-bb33-40ef-93e4-8025a1e31145')}

[chrisberks]

You can use a Dataloader for this.

I'm assuming the userId you'd be passing to the hasUserLiked field is the currently authenticated/logged in user. If that's the case you can use the authenticated_userid, or equivalent in your framework, instead. This would mean you can remove the userId input from the hasUserLiked field.

# loaders.py
from promise import Promise
from promise.dataloader import DataLoader

from database import db_session

def get_user_liked_loader(user_id):
    class UserLikedLoader(DataLoader):

        def batch_load_fn(self, blog_ids):
            query = (
                db_session.query(Like.blog_id)
                .filter(Like.user_id = user_id)
                .filter(Like.blog_id.in_(set(blog_ids)))
            )
            # create a set containing the blog_ids the user has liked
            user_likes = { blog_id for blog_id in query.all() }
            return Promise.resolve([blog_id in user_likes for blog_id in blog_ids])

    return UserLikedLoader()

# schema.py
class Blog(SQLAlchemyObjectType):
    class Meta:
        model = models.Blog
        interfaces = (Node,)

    has_user_liked = graphene.Boolean

    def resolve_has_user_liked(self: models.Blog, info):
        user_liked_loader = info.context.get("user_liked_loader")
        return user_liked_loader.load(self.blog_id)

You'd probably want to create your Dataloader per request. I think this is how it's done in Flask, and I think you get the authenticated_userid with g.user.id. I could be, probably am, wrong on the Flask front.

# app.py
app.add_url_rule(
    '/graphql',
    view_func=GraphQLView.as_view(
        'graphql',
        context={ "user_liked_loader": get_user_liked_loader(authenticated_userid) },
        schema=schema,
    ),
)

[cglacet]

I'm assuming the userId you'd be passing to the hasUserLiked field is the currently authenticated/logged in user. If that's the case you can use the authenticated_userid, or equivalent in your framework, instead. This would mean you can remove the userId input from the hasUserLiked field.

In practice we have a Firebase token from which we retrieve the user ID, from this we build a user object that we have available in every request (and therefore available in every graphql resolver). That's how Starlette handles user authentication (a backend that returns users/scopes).

I think you missed my question (or I don't understand your answer), I'm concerned about SQL requests not about the python side of things, this is a toy example that shows the problem I'm afraid we might have (many request where a single could do the job). I just want to make sure this is what is expected (good practice) when using the graphene-sqlalchemy bridge.

[chrisberks]

I forgot to point out the benefit of using the Dataloader.

By using the Dataloader there will only be two SQL queries, one to get the blogs and one to resolve the hasUserLiked field for every blog—however many there are.

As you're using Starlette you may want to use aiodataloader (Asyncio DataLoader).

If you're not familiar with Dataloaders I'd recommend taking a look at the README of the JavaScript/reference implementation of Dataloader. The Dataloader video from Lee Byron, linked at the bottom of the README, is also super helpful.

The README of aiodataloader is the same, I think, but with Python code examples. The Using with GraphQL section should explain what's going on in the code example I gave.

[cglacet]

@chrisberks oh, I see. Indeed I totally missed that part, glad I asked for clarifications 😄.

I'll need to spend a bit of time to investigate this, but it looks like its exactly what I was looking for. Thanks!

[erikwrede]

I'm closing this since everything seems to be resolved. If there's any remaining questions, feel free to reply or open a new issue.

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