CODEWITHSUNDEEP
ruby-on-railsactiverecordsubqueryhas-manyhas-one-through
Xenor
Xenor

Reputation: 146

ActiveRecord custom has_one relations

I'm using Rails 5.0.0.1 ATM and i've come across issue with ActiveRecord relations when optimizing count of my DB requests. Right now I have: Model A (let's say 'Orders'), Model B ('OrderDispatches'), Model C ('Person') and Model D ('PersonVersion').

Table 'people' consists only of 'id' and 'hidden' flag, rest of the people data sits in 'person_versions' ('name', 'surname' and some things that can change over time, like scientific title).

Every Order has 'receiving_person_id' as for the person which recorded order in DB and every OrderDispatch has 'dispatching_person_id' for the person, which delivered order. Also Order and OrderDispatch have creation time.

One Order has many dispatches.

The straightforward relations thus is:

has_many :receiving_person, through: :person, foreign_key: "receiving_person_id", class_name: 'PersonVersion'

But when I list my order with according dispatches I have to deal with N+1 situation, because to find accurate (according to the creation date of Order/OrderDispatch) PersonVersion for every receiving_person_id and dispatching_person_id I'm making another requests.

SELECT *
FROM person_versions
WHERE effective_date_from <= ? AND person_id = ?
ORDER BY effective_date_from
LIMIT 1

First '?' is Order/OrderDispatch creation date and second '?' is receiving/ordering person id.

Using this query I'm getting accurate person data for the time of Order/OrderDispatch creation.

It's fairly easy to write query with subquery (or subqueries, as Order comes with OrderDispatches on one list) in raw SQL, but I have no idea how to do that using ActiveRecord.

I tried to write custom has_one relation as this is as far as I've come:

has_one :receiving_person. -> {
    where("person_versions.id = (
        SELECT id 
        FROM person_versions sub_pv1 
        WHERE sub_pv1.date_from <= orders.receive_date 
            AND sub_pv1.person_id = orders.receiving_person_id 
        LIMIT 1)")}, 
    through: :person, class_name: "PersonVersion", primary_key: "person_id", source: :person_version

It works if I use this only for receiving or dispatching person. When I try to eager_load this for joined orders and order_dispatches tables then one of 'person_versions' has to be aliased and in my custom where clause it isn't (no way to predict if it's gonna be aliased or not, it's used both ways).

Different aproach would be this:

has_one :receiving_person, -> {
    where(:id => PersonVersion.where("
        person_versions.date_from <= orders.receive_date 
             AND person_versions.person_id = orders.receiving_person_id").order(date_from: :desc).limit(1)}, 
through: :person, class_name: "PersonVersion", primary_key: "person_id", source: :person_version

Raw 'person_versions' in where is OK, because it's in subquery and using symbol ':id' makes raw SQL get correct aliases for person_versions table joined to orders and order_dispatches, but I get 'IN' instead of 'eqauls' for person_versions.id xx subquery and MySQL can't do LIMIT in subqueries which are used with IN/ANY/ALL statements, so I just get random person_version.

So TL;DR I need to transform 'has_many through' to 'has_one' using custom 'where' clause which looks for newest record amongst those which date is lower than originating record creation.

EDIT: Another TL;DR for simplification

def receiving_person
    receiving_person_id = self.receiving_person_id
    receive_date = self.receive_date
    PersonVersion.where(:person_id => receiving_person_id, :hidden => 0).where.has{date_from <= receive_date}.order(date_from: :desc, id: :desc).first
end

I need this method converted to 'has_one' relation so that i could 'eager_load' this.

Upvotes: 1

Views: 2969

Answers (1)

ilan berci
ilan berci

Reputation: 3881

I would change your schema as it's conflicting with your business domain, restructuring it would alleviate your n+1 problem

class Person < ActiveRecord::Base
  has_many :versions, class_name: PersonVersion, dependent: :destroy
  has_one :current_version, class_name: PersonVersion
end

class PersonVersion < ActiveRecord::Base
  belongs_to :person, inverse_of: :versions, 

  default_scope ->{
    order("person_versions.id desc")
  }
end

class Order < ActiveRecord::Base
  has_many :order_dispatches, dependent: :destroy
end

class OrderDispatch < ActiveRecord::Base
  belongs_to :order
  belongs_to :receiving_person_version, class_name: PersonVersion
  has_one :receiving_person, through: :receiving_person_version
end

Upvotes: 0

Related Questions