Rails Scope: Select Distinct Title With Values

Question

I'm having trouble with an SQL query in a scope. I need to return a collection of unique Project.titles and their accompanying ids for use in a form.

I can get the distinct title using

scope :unique_title, select("DISTINCT title")

But I don't get the value in the resulting options

= project_form.input :id, collection: current_user.projects.unique_title

Result:

<select>
  <option value>Item 1</option>
  <option value>Item 2</option>
  <option value>Item 3</option>
</select>

So, adding id to my scope:

scope :unique_title, select("DISTINCT title").select("id")

Result gives me the values but now my DISTINCT selection is defunct:

<select>
  <option value="1">Item 1</option>
  <option value="2">Item 2</option>
  <option value="3">Item 3</option>
  <option value="4">Item 2</option>
  <option value="5">Item 2</option>
  <option value="6">Item 2</option>
</select>

Answer 1

I think you will not be able to select another field along with a Distinct select retaining its distinctiveness.

I think you might be looking for GROUP BY which can be used like this in Rails:

scope :unique_title, select("id, title").group("title")

But, this will select only the first from the group which have the same title. If you want all the records but need to group them according to their title, you will need to fetch all records and afterwards group them from Ruby.

scope :titles, select("id, title")

then where you use the scope, you sh:

Model.titles.all.group_by(&:title).each do |distinct_title, records|
  # do something with the distinct title and records having that distinct title
end