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? -- Posted via http://www.ruby-forum.com/.
Lon Baker 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 Susser http://blog.hasmanythrough.com -- Posted via http://www.ruby-forum.com/.
Josh Susser 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`) ) -- Posted via http://www.ruby-forum.com/.
have you tried acts_as_tree instead? On 23/05/06, Lon Baker <lon@speedymac.com> wrote:> Josh Susser 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`) > ) > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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 -- Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/.
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 On 23/05/06, Lon Baker <lon@speedymac.com> wrote:> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >