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?
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
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
)
)
have you tried acts_as_tree instead?
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 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
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
Thanks. I will take a look at acts_as_nested_set.