CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5
DollarChills
DollarChills

Reputation: 1086

Rails 5, Postgesql and grouping by association

I'm struggling to get my head around Postgesql and grouping by association.

I have a trial which has_many repetitions. I want to group by a column in repetitions to use in a variable. Currently i have:

@trials = Trial.joins(:repetitions).group('repetitions.repetition_index')

But get this error.

PG::GroupingError: ERROR:  column "trials.id" must appear in the GROUP BY clause or be used in an aggregate function

Now from reading up about this i need to include trials.id in my group method, but when I do the output isn't grouped by repetitions.repetition_index anymore. It seems as though it groups by trials.id.

How do I go about making sure that group is only by repetitions.repetition_index?

Update

Trying:

@trials = Trial.joins(:repetitions).select('repetitions.repetition_index,repetitions.treatment_index').group('trials.id, repetitions.repetition_index,repetitions.treatment_index')

And calling:

<% @trials.each do |r| %>
    <table class="table table-bordered">
        <tr>
            <td><%= r.repetition_index %></td>
            <td><%= r.treatment_index %></td>
        </tr>
    </table>
<% end %>

Gives me an output of:

|1|1|
-----
|1|2|
-----
|2|1|
-----
|2|2|
-----

When I'm looking to get:

| |1|
|1|2|
-----
| |1|
|2|2|
-----

Upvotes: 0

Views: 262

Answers (1)

Vishal
Vishal

Reputation: 7361

If you want to get rid of this error you can do 

@trials = Trial.joins(:repetitions).group('trials.id, repetitions.repetition_index')

If you don't want to group by trails.id and want to group by repetitions.repetition_index you have to select only repetitions.repetition_index from query like

@trials = Trial.joins(:repetitions).select('repetitions.repetition_index').group('repetitions.repetition_index')

Let me know if you are clear or not

Update As per your updated question i think you need something like below. query isn't tested . let me know if its not working

 Trial.
 joins(:repetitions).
 select('repetitions.repetition_index,repetitions.treatment_index').
 group_by {
    |repetition| repetitions.repetition_index
  }

Upvotes: 1

Related Questions