0

I am omitting parents that have no children and doing this in the view:

 <% @books.each do |b| %>
 <% if b.comments.empty? %>
 <% else %>
.....

My controller:

@pagy, @books = pagy_countless(Book.where(user_id: current_user.id).includes(:comments).order("comments.created_at DESC").group("comments.created_at, books.id"), items:10 )

And I am using pagy gem.

The issue is that the first page of the pagination is empty, with all the parents and their children showing on the second page. This is because I am omitting childless parents from the view and there are enough such parents that come on top that the first page is entirely empty.

How can I omit childless parents from the controller?

superbot
  • 401
  • 3
  • 19

2 Answers2

0

What you're doing is iterating over the collection, and not displaying the book if its comments are empty. This will indeed give you rows empty output, which is not what you want.

What you want is a query that will give you a collection containing only those books with comments.

Depending on how your associations are set up, you may be able to do this with a left outer join:

# books_controller.rb
def index
  @books = current_user.books.left_join(:comments).distinct(:id).where.not(comments: {id: nil})
  @pagy, @books = paginate(@books, items: 10)
end

This will generate SQL something like:

SELECT DISTINCT "books".* FROM "books" 
LEFT OUTER JOIN "comments" ON "comments"."member_id" = "books"."id" 
WHERE "books"."user_id" = $1 
AND "comments"."id" IS NOT NULL
LIMIT $2 OFFSET $3 [["user_id", "123"], ["LIMIT", 10], ["OFFSET", 0]]

Then, because your collection will only contain books with comments, you can simply render the whole collection:

<!-- books/index.html.erb -->
<= render collection: @books %>
Ryenski
  • 9,582
  • 3
  • 43
  • 47
  • Thank you for your answer. What's good about this is that it gets rid of the childless parents. But it gives duplicate parents. I had switched to ```includes(...)``` for this reason, but ```includes(...)``` shows childless parents. If you know another way to do this, I'd really appreciate it. – superbot Aug 03 '21 at 01:03
  • My question that led me to use ```includes```: https://stackoverflow.com/questions/68605659/rails-duplicate-models-show-on-template/68612955#68612955 – superbot Aug 03 '21 at 01:05
  • You are right, an inner join would give you dups on the books. Maybe you need a left join, combined with distinct, which should give you only unique books. I've updated the answer. – Ryenski Aug 03 '21 at 01:30
  • FYI this query is the opposite of this answer: https://stackoverflow.com/a/5570221/155826 – Ryenski Aug 03 '21 at 01:32
  • Thank you. For some reason I can't now order this by comment created date. I get this error: ```PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ... "comments"."id" IS NOT NULL ORDER BY comm... ^``` – superbot Aug 03 '21 at 02:10
  • I added this to the second line: ```.order("comments.created_at DESC")```. I deleted the grouping. – superbot Aug 03 '21 at 02:11
0

If you want to only fetch records with a match in the join table you want to use an INNER JOIN:

@books = Book.joins(:comments)

.includes, .eager_load, .left_join and .preload create an OUTER JOIN.

If you want to use an INNER JOIN yet avoid those pesky N+1 queries you can use .joins together with .includes or .eager_load:

@books = Book.joins(:comments)
             .includes(:comments)
max
  • 96,212
  • 14
  • 104
  • 165
  • Thank you for your reply, max. The issue here is actually that while ```@books = current_user.books.left_join(:comments).distinct(:id).where.not(comments: {id: nil})``` and ```@pagy, @books = paginate(@books, items: 10)``` shows distinct values, when I add ```.order("comments.created_at DESC")``` the error message shows: ```PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ... "comments"."id" IS NOT NULL ORDER BY comm...```. If you know a solution, I'd really appreciate it. – superbot Aug 06 '21 at 16:07
  • To clarify, this issue arose after implementing Ryan's solution, which partially solves my original issue (if not for the above error). – superbot Aug 06 '21 at 16:09