Retrieving Objects from the Database (Advanced Array Conditions)

Question

I have a model, in which Tags are connected with other Tags through Tagrelationships. A certain Tag may have one parent-Tag and several child-Tags. This is the Tag-model:

    has_many :tagrelationships, :foreign_key => "parent_id",
                                :dependent => :destroy
    has_many :children, :through => :tagrelationships,
                        :source => :child

    has_one :reverse_tagrelationship, :foreign_key => "child_id",
                                      :class_name => "Tagrelationship",
                                      :dependent => :destroy
    has_one :parent,  :through => :reverse_tagrelationship,
                      :source => :parent

The Tagrelationship-model:

      belongs_to :child,  :class_name => "Tag"
      belongs_to :parent, :class_name => "Tag"

The database structure: tags have the following columns: id, name, user_id, created_at, updated_at tagrelationships have the columns: id, parent_id, child_id, created_at, updated_at

I couldn't find out how to pick the Tags which haven't got any parent-Tags. Of course it's possible by picking all Tags of a certain user and evaluating these in a loop:

    @tags = Tag.where(:user_id => current_user)

    @tags.each do |f|
      if f.parent.nil?
        @roottags << f
      end
    end

The array @roottags contains the elements I'm looking for. I'm sure there's an easier way to pick the tags without any parent-elements within one sql-query.

twmills · Accepted Answer · 2011-05-13 13:43:52Z

1

Here's an alternative suggestion that may meet your requirements.

Instead of creating a separate relationship class, what about performing a self-join by adding a parent_id to the Tag class.

This simplifies the relationships in the following ways:

class Tag < ActiveRecord::Base
  has_many :children, :class_name => "Tag", :foreign_key => "parent_id"  
  belongs_to :parent, :class_name => "Tag"

  scope :top_level, where(:parent_id => nil)
end

Now when you want to find the Tags that don't have any parents (i.e. top level tags), you can just use the named scope that filters tags that have parent ID's.

Hope that helps.

edited May 13, 2011 at 13:43

answered May 12, 2011 at 15:07

twmills

3,01525 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tsoenke Over a year ago

In both cases I get the following error-message: 'SQLite3::SQLException: no such column: tags.parent_id: SELECT COUNT(*) FROM "tags" WHERE ("tags"."parent_id" IS NULL)'

twmills Over a year ago

I rewrote my answer to better address your question.

tsoenke Over a year ago

Of course your recommended solution works, but I don't want to change the relationship. If there doesn't exist a simpler solution for the query I would stick to the one described in the question.

Collectives™ on Stack Overflow

Retrieving Objects from the Database (Advanced Array Conditions)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related