Problems with Rails 3 Active Record Query Interface with join on ID

Question

I have been having a problem with the Rails 3 Active Record Query Interface. I have a lookup table (lookups), a Main table (through_references), and a through/join table called through_tables. Thus this is a HABTM configuration that I have set up using has_many :through.

Update: Of special note here is that when I am doing these joins, I have been joining on IDs, to provide filtering of records. It seems that this does not work with Active Record Query Interface. If you do not want to see the gory details of my travails, you can skip down to see my workaround below.

We are also going to have a number of Main Items (through_references table) should be able to have any combination of lookup items, and to conveniently be able to click the relevant lookup items say through check boxes.

I have posted the code on github. There is quite a lot more explanations on the github source code. to see the results, go to the lookups index page. Note that you will need to create the records using the scaffold code.

I also have the code up and running on heroku, with more explanations and examples.

class Lookup < ActiveRecord::Base
  has_many :fk_references
  has_many :through_tables
  has_many :through_references, :through => :through_tables
  attr_accessible :name, :value
end

class ThroughTable < ActiveRecord::Base
  belongs_to :through_reference
  belongs_to :lookup
  attr_accessible :description, :through_reference_id, :lookup_id
end

class ThroughReference < ActiveRecord::Base
  has_many :through_tables
  has_many :lookups, :through => :through_tables
  attr_accessible :description
end

If we want to have a listing if all the lookup items, and the Main Items that correspond with them, we can LEFT JOIN the ‘lookups’ table with the Main Items (through_references) table. Corresponding SQL:

SELECT * FROM lookups
  LEFT OUTER JOIN through_tables ON (lookups.id = through_tables.lookup_id AND through_tables.through_reference_id = 1)
  LEFT OUTER JOIN through_references ON through_references.id = through_tables.through_reference_id
  ORDER BY lookups.id

Returned records:

1;“Lookup Item 1”;“1”;“2012-06-06 17:14:40.819791”;“2012-06-06 17:14:40.819791”;1;1;1;“Main Item 1 has Lookup item 1”;“2012-06-06 17:17:31.355425”;“2012-06-06 17:17:31.355425”;1;“Main Item 1”;“2012-06-06 17:16:30.004375”;“2012-06-06 17:16:30.004375”

2;“Lookup Item 2”;“2”;“2012-06-06 17:14:59.584756”;“2012-06-06 17:14:59.584756”;;;;“”;“”;“”;;“”;“”;“”

3;“Lookup Item 3”;“3”;“2012-06-06 17:15:14.700239”;“2012-06-06 17:15:14.700239”;2;1;3;“Main Item 1 has Lookup item 3”;“2012-06-06 17:17:53.169715”;“2012-06-06 17:17:53.169715”;1;“Main Item 1”;“2012-06-06 17:16:30.004375”;“2012-06-06 17:16:30.004375”

This is what I expected.

=== Active Record Query Interface using custom left join

Lookup.joins(“LEFT OUTER JOIN through_tables ON (lookups.id = through_tables.lookup_id AND through_tables.through_reference_id = 1)” ).includes(:through_references).order(‘lookups.id’)

What is returned from Active Record Query Interface (note I navigate down through the Active Record hierarchy):

Lookup ID Lookup Name Lookup Value Through Table ID Through Table Description Main Item ID Main Item Description
1 Lookup Item 1 1 1 Main Item 1 has Lookup item 1 1 Main Item 1
1 Lookup Item 1 1 3 Main Item 2 has Lookup item 1 2 Main Item 2
2 Lookup Item 2 2 4 Main Item 2 has Lookup item 2 2 Main Item 2
3 Lookup Item 3 3 2 Main Item 1 has Lookup item 3 1 Main Item 1

This is NOT what I expected.

What we have here is identical to the simple left join (without the AND clause). This tells me that the AND clause is being ignored in the Active Record Query Interface.

=== Active Record Query Interface using find_by_sql approach

Lookup.find_by_sql("SELECT * FROM lookups LEFT OUTER JOIN through_tables ON (through_tables.lookup_id = lookups.id AND through_tables.through_reference_id = 1) LEFT OUTER JOIN through_references ON through_references.id = through_tables.through_reference_id ORDER BY lookups.value, through_references.id" )

What is returned from Active Record Query Interface (note I navigate down through the Active Record hierarchy)::

Lookup ID   Lookup Name     Lookup Value    Through Table ID    Through Table Description   Main Item ID    Main Item Description
1   Lookup Item 1   1   3   Main Item 2 has Lookup item 1   2   Main Item 2
1   Lookup Item 1   1   1   Main Item 1 has Lookup item 1   1   Main Item 1
    Lookup Item 2   2   No through_tables entry
1   Lookup Item 3   3   3   Main Item 2 has Lookup item 1   2   Main Item 2
1   Lookup Item 3   3   1   Main Item 1 has Lookup item 1   1   Main Item 1

The results here are crazy!

Is this a BUG, is this the intended effects, or am I missing something ?

I hope there is a clean way of doing this, without having to generate two result sets, and merge them by code.

Taylored Web Sites Taylored Web Sites · Accepted Answer · 2013-01-23T17:44:41

I have found a work-around. The issue seems to be that Active Record will not recognize joins that filter on an ID (LEFT OUTER JOIN xyz ON xyz.id = ID).

My work-around involves creating a stored procedure or function that takes the ID in as a parameter, does the join in the Database, and returns a nice flat recordset.

see: Heroku demo page (skip to bottom)

Note, I am not marking this as a solution, because this is a work-around, and nothing to do with active record.

Problems with Rails 3 Active Record Query Interface with join on ID

2 Answers