0

In a rails application I need to return an ActiveRecord collection containing multiple columns, but only 1 column must be unique. There is a default scope which is not the unique value.

Default Scope

default_scope { order('executed_at DESC') }

Current query WITHOUT unique :terms.

Search.where(organization_id: @my_array_of_ids)
      .executed_after(from_date_time)
      .limit(100)
      .pluck(:terms)

executed_after(from_date_time) is a scope as well. How can I alter the above query to return unique terms without using the .uniq method? I want this to be a single SQL statement.

cheeseandpepper
  • 385
  • 4
  • 16

1 Answers1

0

I also found this:

Convert SQL query to Active Record query in Rails 4

Notice the Model.distinct(:column)

EDIT:

I'd guess it is something like this, but I'd need more info:

Search.distinct(:terms).select(:executed_at)
.where(organization_id: @my_array_of_ids)
  .executed_after(from_date_time)
  .limit(100)
  .group(:executed_at)
Community
  • 1
  • 1
Marcelo Risoli
  • 792
  • 7
  • 24
  • This gives me the error `PG::GroupingError: ERROR: column "searches.executed_at" must appear in the GROUP BY clause or be used in an aggregate function` I think it has something to do with the default scope – cheeseandpepper Oct 06 '14 at 16:14
  • That is an SQL error, you need to add the column to a group by clause with `.group(:executed_at)` – Marcelo Risoli Oct 06 '14 at 16:16
  • I've tried that, the problem is that executed_at is a time stamp with all unique results. I don't care if these are unique or not; only if the terms are unique. – cheeseandpepper Oct 06 '14 at 16:31
  • But the executed_at column is in the select clause is that correct? In case it is in the select clause, despite it not being one of the distinct parameters, it must be in the group by clause, since it is all unique, it will just not group any records. – Marcelo Risoli Oct 06 '14 at 16:39
  • I believe the default scope makes it part of the select clause. `SELECT terms, executed_at GROUP BY terms, executed_at` will return all results given that executed at is a time stamp. I need a way around this – cheeseandpepper Oct 06 '14 at 16:51
  • What precisely needs to be returned given the unique column? – Marcelo Risoli Oct 06 '14 at 16:55
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62537/discussion-between-marcelo-risoli-and-cheeseandpepper). – Marcelo Risoli Oct 06 '14 at 16:58