Forum: Ruby on Rails self-referencing has_many

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Lon B. (Guest)
on 2006-05-24 01:40
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?
Josh S. (Guest)
on 2006-05-24 02:07
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
Lon B. (Guest)
on 2006-05-24 02:13
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`)
)
unknown (Guest)
on 2006-05-24 02:19
(Received via mailing list)
have you tried acts_as_tree instead?
Lon B. (Guest)
on 2006-05-24 02:48
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
Lon B. (Guest)
on 2006-05-24 03:02
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.
unknown (Guest)
on 2006-05-25 17:10
(Received via mailing list)
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
Lon B. (Guest)
on 2006-05-25 18:31
Thanks. I will take a look at acts_as_nested_set.
This topic is locked and can not be replied to.