CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-4
Tuesday Four AM
Tuesday Four AM

Reputation: 1276

Rails select by number of associated records

I have the following models in my rails app:

class Student < ApplicationRecord
  has_many :tickets, dependent: :destroy
  has_and_belongs_to_many :articles, dependent: :destroy

class Article < ApplicationRecord
  has_and_belongs_to_many :students, dependent: :destroy

class Ticket < ApplicationRecord
  belongs_to :student, touch: true

I wish to extract:

all Students who have less than X articles

and

all Students whose last ticket title is 'Something'.

Everything I tried so far takes a lot of time. I tried mapping and looping through all Students. But I guess what I need is a joined request. I am looking for the most efficient way to do it, as the database I am working with is quite large.

Upvotes: 0

Views: 673

Answers (3)

MCI
MCI

Reputation: 922

You asked "I need to extract all Students who has less than articles". I'll presume you meant "I need to extract all Students who have less than X articles". In that case, you want group and having https://guides.rubyonrails.org/active_record_querying.html#group.

For example, Article.group(:student_id).having('count(articles.id) > X').pluck(:student_id).

To address your second question, you can use eager loading https://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations to speed up your code. 

result = students.filter do |student|
  students.tickets.last.name == 'Something'
end

Upvotes: 2

Praveen
Praveen

Reputation: 250

Here association is HABTM so below query should work

x = 10
Student.joins(:articles).group("articles_students.student_id").having("count(articles.id) < ?",x)

Upvotes: 1

dileep nandanam
dileep nandanam

Reputation: 2895

go with @MCI's answer for your first question. But a filter/select/find_all or whatever (although I havn't heared about filter method in ruby) through students record takes n queries where n is the number of student records (called an n+1 query).

studs = Student.find_by_sql(%{select tmp.id from (
  select student_id as id from tickets where name='Something' order by tickets.created_at desc
) tmp group by tmp.id})

Upvotes: 2

Related Questions