Join query returning duplicate results

Issues
1

Hi, I have a question about expected behavior with joins and combines in rom-sql. I’ve noticed that when joining with has_many relationships, rom will return a duplicate of the parent record for each child record.

I know that raw sql join queries will return duplicate rows for each child record, but is rom supposed to de-duplicate these results? Or is this expected behavior?

For example, imagine a database where a post can have many comments. My goal is to find all posts with at least one comment from the current user, but I want each post to be returned with comments from any user.

The query below almost achieves this, but it will return the same post 3 times in the result array if there are 3 comments related to the post with a matching author_id. I could try de-duping manually, but I need to paginate the results and this would throw off the counts used by the pager.

Is there a way to have rom de-duplicate the results?

class PostRepo < ROM::Repository[:posts]
  def posts_with_comments_by(user_id, page: 1)
    result = posts
      .join(comments)
      .where(comments[:author_id].is(user_id))
      .combine(:comments)
      .per_page(50)
      .page(page)
   [result.to_a, result.pager]
  end
end

UPDATE: Looks like distinct achieves exactly what I want. Also, if you have an order by clause that uses a sql function, apparently postgres needs that specified in the distinct call. Example:

class PostRepo < ROM::Repository[:posts]
 def posts_with_comments_by(user_id, page: 1)
   result = posts
     .join(comments)
     .distinct { lower(title) }
     .where(comments[:author_id].is(user_id))
     .combine(:comments)
     .order { lower(title) }
     .per_page(50)
     .page(page)
  [result.to_a, result.pager]
 end
end
1 Like
2

I’m glad that you figured out the query. When it comes to optimizing struct allocations, I’m planning to add support for Identity Map that would ensure that only unique structs are being loaded when materializing a relation (which can be a combined relation, of course).

1 Like