Rails select by number of associated records

Question

I have 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 need to extract all Students who has less than articles and I need to extract all Students who's 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 database I am working with is quite large.

do you really need a dependent: :destroy for students association in article which cause deletion of student record on deletion of corresponding article. — dileep nandanam, Jun 21 '19 at 02:01
@DileepNandanam As far as I understand, only record of joined table is deleted not the records themselves. — Tuesday Four AM, Jun 21 '19 at 06:17
@TuesdayFourAM it soesn't do either of them for HABTM associations, see https://stackoverflow.com/questions/2799746/habtm-relationship-does-not-support-dependent-option — dileep nandanam, Jun 21 '19 at 08:26

MCI · Answer 1 · 2019-06-21T19:25:17.373

2

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

edited Jun 21 '19 at 19:25

answered Jun 21 '19 at 00:47

MCI

888
1
7
13

Thank you. I am getting error though ActiveRecord::StatementInvalid (PG::GroupingError: ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "articles".* FROM "articles" GROUP BY "articles"."stu... ^ : SELECT "articles".* FROM "articles" GROUP BY "articles"."student_id" HAVING (count(articles.id) < '5')): – Tuesday Four AM Jun 21 '19 at 08:36
Than you for the hint regarding the eager loading - it worked out for me: studs.each do |client| t = client.tickets.last if t && pt.include?(t.pt) ticket_res << client puts "Client with payment: #{client.name}, #{client.id}, #{client.tickets.last.pt}" end end – Tuesday Four AM Jun 21 '19 at 09:02
Ah, I edited the answer and added the `.pluck(:student_id)` to address Postgres's complaints. This https://stackoverflow.com/questions/16418504/pgerror-in-group-by-clause post explains why you need it – MCI Jun 21 '19 at 19:27

dileep nandanam · Answer 2 · 2019-06-21T11:04:48.203

2

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

edited Jun 21 '19 at 11:04

answered Jun 21 '19 at 01:59

dileep nandanam

2,827
18
20

Thank you. Problem is I need to select only if the last record has that name, not any of them. – Tuesday Four AM Jun 21 '19 at 06:19

score 1 · Answer 3 · answered Jun 21 '19 at 14:19

1

Here association is HABTM so below query should work

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

answered Jun 21 '19 at 14:19

Praveen

240
1
5

Rails select by number of associated records

3 Answers3