How can I turn an instance method into an association?-CodePudding

Model Subscription has_many :versions.

I'm writing a query to fetch subscriptions and order them by the associated versions last authorized_at date, but I'm not sure the query can return the same subscription more than once due to the joins and group statements.

Subscription
      .joins(:versions)
      .group("subscriptions.id, users.id")
      .order("MAX(versions.authorized_at) ASC")

On the other hand, I have the Subscription instance method current_version that returns the last authorized version .

subscription.rb

  def current_version
    versions.authorized.last
  end

I think an association (instead of the instance method) could help me so then I could join the Subscription and Version tables and order the results by the associated table authorized date.

But how do I manage to write an association that performs the query like the class method does?

I tried doing something like:

has_one     :current_version,
              -> { versions.authorized.last },
              class_name: "Version", inverse_of: "Subscription"

But I'm getting NameError: undefined local variable or method 'versions' for #<Version::ActiveRecord_Relation

CodePudding user response：

The best solution for read performance is to add a separate foreign key column to the table as a short cut:

add_reference :subscriptions, 
              :current_version,
              foreign_key: {
                to_table: :versions  
             }

class Subscription < ApplicationRecord
  has_many :versions, 
    after_add: :set_current_version
  belongs_to :current_version,
    class_name: 'Version'

  def set_current_version(version)
     update_attribute(:current_version_id, version.id) 
  end
end

This uses an assocation callback to set the association but you can also handle it with a DB trigger or a service object.

If you really want to use has_one you need to use hackery like a subquery, window function or a laterial join:

class Subscription
  has_one :current_version, 
    -> {
      where(
        id: Version.select(:id)
                   .group(:subscription_id)
                   .order("MAX(versions.authorized_at) ASC")
      )
    },
    class_name: 'Version'
end

CodePudding user response：

I tried the above answer by max and it worked in console (I only tried the has_one proposition), but I couldn't manage to get the views working (I kept getting undefined table errors).

In the end I created a scope to fetch the subscription with it's current_version (last authorized version) and used that.

  scope :with_current_version, -> {
    select("subscriptions.*, last_version.authorized_at AS last_version_authorized_at")
      .joins("LEFT OUTER JOIN (SELECT DISTINCT ON (subscription_id) * FROM versions
      ORDER BY versions.subscription_id,
      versions.authorized_at DESC) AS last_version
      ON last_version.subscription_id = subscriptions.id")
  }

And then in my controller:

@subscriptions = current_account.subscriptions.with_current_version
                         .order("last_version_authorized_at ASC")