findOptions based hasMany Query creates recursive unions

[mschipperheyn]

I have the following domain objects

post hasMany likes

The Like type has a number of types based on it (not just post, but also course, etc)

interface Like {
   id: ID!
   user: User!
}
type PostLike implements Node & Like {
    id: ID!
    user: User!
    post: Post!
}
type CourseLike implements Node & Like {
    id: ID!
    user: User!
    course: Course!
}
type Post {
   postInteraction: PostInteraction!
   user: User!
}
type PostInteraction {
    likes: [PostLike!]!
}

I use the following resolver for Post

   Post: {
       postInteraction: (...args) => [args[0], args[1]],
   },
   PostInteraction: {
        likes: (parentArray, args, context, info) => {
          return resolver(() => models.Post.associations.likes, {
            before: findOptions => {
                findOptions.order = [['createdAt', 'DESC']];
                findOptions.limit = 3;
                return findOptions;
            },
          })(parentArray[0], parentArray[1], context, info);
    },
   }

And this is the query I see:

SELECT `Like`.* FROM (
    SELECT * FROM (
        SELECT `id`, `createdAt`, `updatedAt`, `userId`, `courseId`, `postId` 
        FROM `Likes` AS `Like` 
        WHERE `Like`.`postId` = '3798f424-3fcb-4f1b-a774-70df691a8545' 
        ORDER BY `Like`.`createdAt` DESC LIMIT 3) 
            AS sub UNION ALL SELECT * FROM (
                SELECT `id`, `createdAt`, `updatedAt`, `userId`, `courseId`, `postId` FROM `Likes` AS `Like` 
                WHERE `Like`.`postId` = '99f75487-3680-4e6d-8224-ac67ad726000' 
                ORDER BY `Like`.`createdAt` DESC LIMIT 3) 
                AS sub UNION ALL SELECT * FROM (
                    [...]

it recurses for as many posts as there are. Seems very inefficient and not the intention of the dataloader.

What I would expect is a simple query retrieving all the likes based on IN (...postIds) and the graphql sorting out the assignment. Is there a better way I can do this?

[mickhansen]

It's because you're using limit, there's no way around it as you're asking for 3 per postIds, grouping all the post ids and limits would not give the intended result.

[mschipperheyn]

@mickhansen I hear what you're saying. I'm wondering if there's any way I could customize this stuff for the intended result.

[mickhansen]

@mschipperheyn How would you express this in SQL? N of subgroup is a common SQL problem.

[mschipperheyn]

Perhaps some intermediary table or view with something like https://mobile.databasejournal.com/features/mysql/selecting-the-top-n-results-by-group-in-mysql.html

[mickhansen]

One Solution that Works: UNION with LIMIT

Is the solution sequelize went with, i think i wrote that code but it's probably been a few years :)

[mickhansen]

It's a tough problem unfortunetaly. In the graphql world you're sometimes better off not using limit if you know the total amount of rows is a reasonable one.