has_some_of_many returns empty when associated with same model

[abhishek8xz]

I have a model that has child records inside the same model. For this model has_some_of_many does not seem to work.

Model:

class Post < ApplicationRecord
  has_many :children, -> { order(created_at: :asc) }, class_name: "Post", foreign_key: "parent_post_id"
  has_some_of_many :first_three_children, -> { order(created_at: :asc).limit(3) },
    class_name: "Post",
    foreign_key: "parent_post_id"
end

This works:

parent_posts = Post.all.where(parent_post_id: nil)
parent_posts.collect{|parent_post| parent_post.children.limit(3).collect(&:name) }

Returns:

  Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" = $1 ORDER BY "posts"."created_at" ASC LIMIT $2  [["parent_post_id", 1], ["LIMIT", 3]]
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" = $1 ORDER BY "posts"."created_at" ASC LIMIT $2  [["parent_post_id", 12], ["LIMIT", 3]]
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" = $1 ORDER BY "posts"."created_at" ASC LIMIT $2  [["parent_post_id", 23], ["LIMIT", 3]]
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" = $1 ORDER BY "posts"."created_at" ASC LIMIT $2  [["parent_post_id", 34], ["LIMIT", 3]]
  Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" = $1 ORDER BY "posts"."created_at" ASC LIMIT $2  [["parent_post_id", 45], ["LIMIT", 3]]
=>
[["Post 0 - Child 0", "Post 0 - Child 1", "Post 0 - Child 2"],
 ["Post 1 - Child 0", "Post 1 - Child 1", "Post 1 - Child 2"],
 ["Post 2 - Child 0", "Post 2 - Child 1", "Post 2 - Child 2"],
 ["Post 3 - Child 0", "Post 3 - Child 1", "Post 3 - Child 2"],
 ["Post 4 - Child 0", "Post 4 - Child 1", "Post 4 - Child 2"]]

This does not work:

parent_posts = Post.all.where(parent_post_id: nil)
parent_posts.includes(:first_three_children).collect{|parent_post| parent_post.first_three_children.collect(&:name) }

Returns:

 Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" IS NULL
  Post Load (0.4ms)  SELECT "posts".* FROM (SELECT "posts"."id" AS parent_post_id_alias, "lateral_table".* FROM "posts" INNER JOIN LATERAL (SELECT "posts".* FROM "posts" WHERE "posts"."parent_post_id" = "posts"."id" ORDER BY "posts"."created_at" ASC LIMIT $1) lateral_table ON TRUE) posts WHERE "posts"."parent_post_id_alias" IN ($2, $3, $4, $5, $6)  [["LIMIT", 3], ["parent_post_id_alias", "1"], ["parent_post_id_alias", "12"], ["parent_post_id_alias", "23"], ["parent_post_id_alias", "34"], ["parent_post_id_alias", "45"]]
=> [[], [], [], [], []]

Note:

I have also created a rails app to play with this issue: https://github.com/abhishek8xz/has_some_of_many_issue/blob/main/README.md

[bensheldon]

Thank you for reporting this! I'll look into it later this week 🙏

[bensheldon]

Just staring at this on my phone, for future self-reference:

I think the problem is that the inner lateral join gets confused about which "posts" table is which. So it probably needs to be aliased, which I think needs to be done with AR because that's the relation scope (rather than Arel):

INNER JOIN LATERAL (
SELECT "posts".* 
FROM "posts" -- this here, I think
WHERE "posts"."parent_post_id" = "posts"."id" 
ORDER BY "posts"."created_at" ASC 
LIMIT $1)