0

I am connecting to one of my company's SQL Server databases, and trying to set up ActiveRecord so I can treat them just the same as Rails objects.

I have these two models:

class Change < ActiveRecord::Base
  belongs_to :affected_contact, class_name: "Contact"
end

class Contact
  # Contact's primary key is a binary UUID; I can't change this
end

I am trying to get the affected contact of one particular change. Normally, this would be a simple case, but:

Change.first.affected_contact
  Change Load (52.6ms)  EXEC sp_executesql N'SELECT TOP (1) [chg].* FROM [chg] ORDER BY [chg].[id] ASC'
  Contact Load (28.0ms)  EXEC sp_executesql N'SELECT TOP (1) [ca_contact].* FROM [ca_contact] WHERE [ca_contact].[contact_uuid] = @0', N'@0 binary', @0 = 0xfcf9a8ac6381aa4386c9b10ee382e10b  [["contact_uuid", "<16 bytes of binary data>"]]
=> nil

... that's not what I want! And yet, if I eager-load the join first, it works:

Change.eager_load(:affected_contact).first.affected_contact
  SQL (34.4ms)  EXEC sp_executesql N'SELECT TOP (1) holy_crap_theres_a_lot_of_columns FROM [chg] LEFT OUTER JOIN [ca_contact] ON [ca_contact].[contact_uuid] = [chg].[affected_contact] ORDER BY [chg].[id] ASC'
=> #<Contact contact_uuid: "\xFC\xF9\xA8\xACc\x81\xAAC\x86\xC9\xB1\x0E\xE3\x82\xE1\v", ... >

In fact, if I force the matching to happen in the JOIN clause in any way, it will work, but belongs_to seems to use the WHERE clause instead, and nil is the best response I can get (a lot of the time, there are conversion errors between the string and its binary type).

Is there a way to ensure eager-loading through the JOIN clause happens by default on the belongs_to association?

PJSCopeland
  • 2,818
  • 1
  • 26
  • 40

2 Answers2

1

I found that #find_by_contact_uuid (contact_uuid being the primary key) worked, where #find didn't, for some reason. That led to this being implemented.

I have ended up essentially rewriting the association methods that Active Record supplies:

module AssociationMethods
  def self.included(base)
    base.reflect_on_all_associations(:belong_to).each do |a|
      define_method a.name do
        # #find_by_<uuid_pk> seems to work where #find doesn't
        a.klass.send "find_by_#{a.association_primary_key}", self[a.foreign_key]
      end
    end

    base.reflect_on_all_associations(:has_many).each do |a|
      define_method a.name do
        a.klass.where(a.foreign_key => self.send(a.association_primary_key))
      end
    end
  end
end

class Contact
  has_many :changes, foreign_key: :affected_contact_id
  include AssociationMethods # include *after* all associations are defined
end

class Change
  belongs_to :affected_contact, class_name: 'Contact'
  include AssociationMethods
end

It doesn't cover everything that Active Record supplies when setting up the associations, but it seems to do the trick.

PJSCopeland
  • 2,818
  • 1
  • 26
  • 40
-1

Using includes should resolve your problem. This is because includes will preload or eager_load depending on your other conditions.

read more here

halfer
  • 19,824
  • 17
  • 99
  • 186
sameera207
  • 16,547
  • 19
  • 87
  • 152
  • That's not my problem. I _need_ to use a `JOIN` query (`eager_load`), because UUIDs are too hard to pass around in `WHERE` clauses. But how do I define the `belongs_to` association to do that? I want to be able to say `change.affected_contact`, rather than `Change.where(id:change.id).eager_load(:affected_contact).first.affected_contact`. – PJSCopeland Apr 20 '15 at 23:22
  • Hi Sameera. I count [over 50 usages of the construction "coz"](https://stackoverflow.com/search?q=user%3A319702+coz) in your posts. If you mean "because", would you use that? We have a lot of people here whose native language is not English, and I fear we are teaching them that SMS abominations are actually real words. – halfer Apr 27 '17 at 09:09