Self-referencing has_many

Rails
#1

Having a devil of a time finding records in a self-referencing has_many
table relationship.

Everything is working find looking at the has_many and the belongs_to
relationship.

But, when I try to find all “orphans”, records that are neither a parent
nor a child, I can not find a query that work in ActiveRecord.

This query works in MySQL:

SELECT * FROM templates
LEFT JOIN templates t2 ON templates.id=t2.template_id
WHERE (t2.id IS NULL AND templates.template_id IS NULL)

Of course, the table alises through ActiveRecord for a loop when
manually entered in the :joins options.

Any suggestions?

#2

Lon B. wrote:

Having a devil of a time finding records in a self-referencing has_many
table relationship.

Everything is working find looking at the has_many and the belongs_to
relationship.

But, when I try to find all “orphans”, records that are neither a parent
nor a child, I can not find a query that work in ActiveRecord.

This query works in MySQL:

SELECT * FROM templates
LEFT JOIN templates t2 ON templates.id=t2.template_id
WHERE (t2.id IS NULL AND templates.template_id IS NULL)

Of course, the table alises through ActiveRecord for a loop when
manually entered in the :joins options.

Any suggestions?

Can you show us your table schemas and model association definitions?

–
Josh S.
http://blog.hasmanythrough.com

#3

Josh S. wrote:

Can you show us your table schemas and model association definitions?

Here it it:

class Template < ActiveRecord::Base
has_many :chains, :foreign_key => ‘template_id’, :class_name =>
‘Template’
belongs_to :master, :foreign_key => ‘template_id’, :class_name =>
‘Template’
end

CREATE TABLE templates (
id int(11) unsigned NOT NULL auto_increment,
site_id int(11) unsigned NOT NULL default ‘0’,
template_id int(11) default NULL,
title varchar(128) NOT NULL default ‘’,
subject varchar(128) NOT NULL default ‘’,
content text NOT NULL,
PRIMARY KEY (id),
KEY site_id (site_id)
)

#4

have you tried acts_as_tree instead?

#5

Found a solution.

Template.find(:all, :conditions => [“t2.id IS NULL AND
templates.template_id IS NULL”], :joins => “LEFT JOIN templates t2 ON
templates.id=t2.template_id”, :select => “templates.*”)

The key was changes the SELECT to include “templates.*”.

Without that ActiveRecord would not populate the object with the
returned values.

#6

unknown wrote:

have you tried acts_as_tree instead?

The touble isn’t the current relationships. The issue is finding records
with no children.

Unless I am missing some feature in acts_as_tree

#7

Oh, sorry. I’ve actually looked at the docs now. I thought
acts_as_tree had something like that. Turns out I was getting confused
with acts_as_nested_set. Speaking of which, I think acts_as_nested_set
would provide a much cleaner solution than you have now
(http://wiki.rubyonrails.com/rails/pages/ActsAsNestedSet). Use
children.count == 0.
-N

#8

Thanks. I will take a look at acts_as_nested_set.