Question 90673 in PostgreSQL Question posted in
The official documentation can be found here.

Why the count alias column is not returned as part of ActiveRecord_Relation? – Postgresql

I have two tables.

StudentGroup and StudentGroupsMembers.

StudentGroup.id = StudentGroupMembers.group_id is the foreign key relation.

sg = StudentGroup.joins(:student_group_members).select(" student_groups.id, student_groups.name, count(student_group_members.id) as cnt ").group('student_group_members.group_id, student_groups.name, student_groups.id').where(id: [3,4,5,6,6,7,7,8,8]).where("student_group_members.member_type='ra'")

This return ActiveRecord_Relation

[#<StudentGroup:0x00007fc11253d088 id: 3, name: "Blah">,
#<StudentGroup:0x00007fc11253cc78 id: 4, name: "Foo">,
#<StudentGroup:0x00007fc11253c7c8 id: 7, name: "Bar">,
#<StudentGroup:0x00007fc11253c390 id: 8, name: "ABc">]

But only when I do

sg[0]["cnt"] I’m able to get cnt value.

  1. Why?
  2. What should I do to get cnt as part of each record, i.e, not doing sg[0]["cnt"]?
Tags:

2

Answers


  1. 0

    Rails returns an ActiveRecord Association as you have noticed. You can use that as if it were a normal record with the joined columns becoming methods like the regular StudentGroup columns.

    sg.first.cnt
# => 5

    The association is returning an array of StudentGroup objects but they have the joined columns already queried, you just don’t see them in the StudentGroup object until you call the method. If you want you can append pluck("student_groups.id, student_groups.name, cnt") to the end of the query:

    sg = StudentGroup.joins(:student_group_members).select(" student_groups.id, student_groups.name, count(student_group_members.id) as cnt ").group('student_group_members.group_id, student_groups.name, student_groups.id').where(id: [3,4,5,6,6,7,7,8,8]).where("student_group_members.member_type='ra'").pluck("student_groups.id, student_groups.name, cnt")

    You will get an array of arrays, each one containing the data you queried. But it is JUST the data from the columns, not a StudentGroup object. So you won’t be able to use all the StudentGroup methods on them…

    [[ 3, "Blah", 5], [4, "Foo", 34], [7, "Bar", 3]....]

    If you use sg in some sort of rails view or method you can iterate over it just like a normal query of StudentGroup, but you will have the joined columns available as methods:

    <% sg.each do |g| %>
     Group ID: <%= g.id %>
     Name:     <%= g.name %>
     Count:    <%= g.cnt %>
<% end %>
    Login or Signup to reply.
  2. 0

    Try this:

    # Use the pluck method to select the cnt values and store them in an array
cnt_values = sg.pluck(:cnt)

# Use the zip method to combine the values of the id and name attributes
# with the cnt values to create a new array of records
sg = sg.zip(cnt_values).map { |group, cnt| group.merge(cnt: cnt) }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search