Databases
StackOverflow2013
Postsdbo
POProblems with Rails 3 Active Record Query Interface with join on ID
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblems with Rails 3 Active Record Query Interface with join on ID
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>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.</p> <p>I have posted the code on <a href="https://github.com/tayloredwebsites/rails3_left_joins" rel="nofollow">github</a>. 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.</p> <p>I also have the code up and running on <a href="http://tws-left-joins.heroku.com/lookups" rel="nofollow">heroku</a>, with more explanations and examples.</p> <pre><code>class Lookup &lt; ActiveRecord::Base has_many :fk_references has_many :through_tables has_many :through_references, :through =&gt; :through_tables attr_accessible :name, :value end class ThroughTable &lt; ActiveRecord::Base belongs_to :through_reference belongs_to :lookup attr_accessible :description, :through_reference_id, :lookup_id end class ThroughReference &lt; ActiveRecord::Base has_many :through_tables has_many :lookups, :through =&gt; :through_tables attr_accessible :description end </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>Returned records:</p> <pre><code>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” </code></pre> <p>This is what I expected.</p> <p>=== Active Record Query Interface using custom left join</p> <pre><code>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’) </code></pre> <p>What is returned from Active Record Query Interface (note I navigate down through the Active Record hierarchy):</p> <pre><code>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 </code></pre> <p>This is NOT what I expected.</p> <p>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.</p> <p>=== Active Record Query Interface using find_by_sql approach</p> <pre><code>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" ) </code></pre> <p>What is returned from Active Record Query Interface (note I navigate down through the Active Record hierarchy)::</p> <pre><code>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 </code></pre> <p>The results here are crazy!</p> <p>Is this a BUG, is this the intended effects, or am I missing something ?</p> <p>I hope there is a clean way of doing this, without having to generate two result sets, and merge them by code.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTaylored Web Sites
    1. USTaylored Web Sites
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProblems with Rails 3 Active Record Query Interface with join on ID
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload