How to convert this query to ActiveRecord logic?

Question

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?

you can refer to this http://stackoverflow.com/questions/18082096/rails-4-scope-to-find-parents-with-no-children — Sonalkumar sute, Mar 30 '15 at 10:38
@Sontya I tryed, but `Topic.includes(:topics).where(topics: { id: nil })` returns always an empty set. — ProGM, Mar 30 '15 at 10:39
@Sontya `Topic.includes(:topics).where(topics: { id: nil }).to_sql` -> "SELECT \"topics\".* FROM \"topics\" WHERE \"topics\".\"id\" IS NULL", that is wrong. — ProGM, Mar 30 '15 at 10:42
@Sontya empty set... to_sql returns: `SELECT "topics".* FROM "topics" INNER JOIN "topics" "topics_topics" ON "topics_topics"."topic_id" = "topics"."id" WHERE "topics_topics"."topic_id" IS NULL` — ProGM, Mar 30 '15 at 10:47

score 1 · Answer 1 · edited Mar 30 '15 at 10:37

1

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 })

edited Mar 30 '15 at 10:37

Marek Lipka

50,622
7
87
91

answered Mar 30 '15 at 10:21

user3118220

1,438
12
16

Maybe you misunderstood the request. This will search for all non-root topics, not for leafs... – ProGM Mar 30 '15 at 10:22
try this: topics.where.not(topic_id: nil) – user3118220 Mar 30 '15 at 10:28
`scope :without_topics, includes(:topics).where(:topics => { :id => nil })` returns always an empty set. – ProGM Mar 30 '15 at 10:41

Sharvy Ahmed · Accepted Answer · 2015-03-30T11:40:15.697

1

Try this:

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

edited Mar 30 '15 at 11:40

answered Mar 30 '15 at 10:39

Sharvy Ahmed

7,247
1
33
46

I edited my question to be more clear... Every sub-topic can have many sub-topics, indefinitely. So a topic with `topic_id != null` is not a leaf, is just a non-parent. I want all topics with no children, not all topics that have a parent. – ProGM Mar 30 '15 at 10:50
See if it finds your expected outputs, we can refactor it later. – Sharvy Ahmed Mar 30 '15 at 11:13
Ok! This obtains the expected results! It's a bit raw, because it uses 2 queries... but I think that it can be improved :) – ProGM Mar 30 '15 at 11:22
Okay, now I can improve it. I was not getting your question in the first place. :) – Sharvy Ahmed Mar 30 '15 at 11:26
No problem, probably it was a bit unclear ;) I finally obtained this: `where.not(id: where('topic_id IS NOT NULL').select(:topic_id))` – ProGM Mar 30 '15 at 11:34

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

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")

edited May 23 '17 at 12:22

Community

1
1

answered Mar 30 '15 at 10:21

archit gupta

954
10
13

`ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE` – ProGM Mar 30 '15 at 10:34

How to convert this query to ActiveRecord logic?

3 Answers3