How to have n-level counts?

[doutatsu]

I've been trying to figure out how to properly set this up, but after a day of no success, was wondering if I am doing this wrong...

If we have 3 tables - Publisher, Book, and Chapter - with Book having chapters_count and Publisher having available_chapters_count, who can I correctly update Publisher count through Book's count?

I've tried different types of queries, but honestly it feels like it's an overkill. All I want is simply copy the count of chapters_count from the book that has most chapters into publisher's available_chapters_count.

class Chapter < ApplicationRecord
  belongs_to :book
  counter_culture :book
end

class Book < ApplicationRecord
  MAX_CHAPTERS_CONDITION = <<-SQL.squish
    books.chapters_count = (
      SELECT max_subquery.chapters_count
      FROM (
        SELECT ms.publisher_id, ms.chapters_count
        FROM books ms
        WHERE ms.pending = FALSE
          AND ms.duplicate = FALSE
        ORDER BY ms.publisher_id, ms.chapters_count DESC
      ) max_subquery
      WHERE max_subquery.publisher_id = books.publisher_id
      LIMIT 1
    )
    AND books.pending = FALSE
    AND books.duplicate = FALSE
  SQL

  belongs_to :publisher

  counter_culture :publisher,
                  column_name: proc { |model| model.max_chapters? ? 'available_chapters_count' : nil },
                  column_names: lambda {
                    { [MAX_CHAPTERS_CONDITION] => 'available_chapters_count' }
                  },
                  delta_column: 'chapters_count'

  def max_chapters?
    chapters_count == publisher.books.maximum(:chapters_count)
  end
end

[magnusvk]

I think I follow what you're trying to do, but unfortunately I'm also not immediately sure whether counter_culture can do that for you and, if so, how. I'm guessing the issue might be that updating counters doesn't trigger callbacks and so when a new chapter updates the book#chapters_count that doesn't then trigger another callback to update the count on the publisher because the callbacks on Book aren't triggered. If that's right then I don't think this approach will work.

Have you tried putting a multi-level counter cache on chapter instead? I'm also not totally sure how that would work, but it would at least avoid the issue of callbacks not getting triggered.

Unfortunately I won't be able to dig deeper on this since I'm not able to supply support on this open source library.

[doutatsu]

I've tried multi-level counter cache, but the performance wasn't good enough, especially as there are millions of chapters.

For anyone looking into it later, I've ended up making it work with the setup above - I just needed to make sure the query is correct:

  MAX_CHAPTERS_CONDITION = <<-SQL.squish
    books.id IN (
      SELECT ms.id
      FROM (
        SELECT id,
              publisher_id,
              chapters_count,
              ROW_NUMBER() OVER (
                PARTITION BY publisher_id
                ORDER BY chapters_count DESC, id ASC
              ) as rn
        FROM books
        WHERE pending = FALSE AND duplicate = FALSE
      ) ms
      WHERE ms.rn = 1
    )
  SQL

I needed to make sure we still get back an ActiveRecord::Relation, but it needs to always be 1. As otherwise when using delta_column, it would add together books with the same number of chapters.

I think my request would be to add something similar to delta_column @magnusvk - my query works, but it would have been nice if it was possible to simply make counter_cache read from the column instead of doing a sum of them

[magnusvk]

I'm always happy to review a PR that adds that functionality but unfortunately I won't be able to work on that kind of addition myself.