CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
ProGM
ProGM

Reputation: 7108

How to convert this query to ActiveRecord logic?

I'm working in rails. My model is like this:

class Topic < ActiveRecord::Base
  has_many :topics, dependent: :delete_all
  belongs_to :parent, foreign_key: 'topic_id', class_name: 'Topic'
  has_many :contents

  validates :name, uniqueness: true, presence: true
end

So I have a topic that can have many "sub-topics". Every sub-topic can have many sub-topics, indefinitely. I'm trying to make a method that returns me all "leaf" topics. A leaf topic is a topic with no sub-topics.

def self.leafs
  where(???)
end

I can't formulate this in active record logic, so actually I use this query:

Topic.find_by_sql("SELECT * FROM topics WHERE id NOT IN (SELECT t.topic_id FROM topics t WHERE topic_id IS NOT NULL)")

How can I write this in an active record way?

Upvotes: 0

Views: 85

Answers (3)

Sharvy Ahmed
Sharvy Ahmed

Reputation: 7405

Try this:

child_ids_with_topic_id = where.not(topic_id: nil).pluck(:topic_id)
where.not(id: child_ids_with_topic_id)

Upvotes: 1

archit gupta
archit gupta

Reputation: 1034

Although i am not sure but i have tried using this Rails 3 finding parents which have no child

scope :leafs, joins("left join topics as sub_topics sub_topics.topic_id = topics.id").where("topics.topic_id is null")

Upvotes: 0

user3118220
user3118220

Reputation: 1468

def self.leafs
  topics.where("topic_id IS NOT NULL")
end

ActiveRecord 4.0 and above adds where.not so you can do this:

scope :leafs, -> topics.where.not(topic_id: nil)


scope :without_topics, includes(:topics).where(:topics => { :id => nil })

Upvotes: 1

Related Questions