Manual sorting in ActiveRecord

Question

Each company in my database has many projects, and every company is given a 'General tasks' project when they sign up. This particular project cannot be deleted by its company, and it is treated as a special repository for tasks that have not yet been assigned to one of the company's other projects.

When displaying the company's projects, I want them to appear in alphabetical order but with the single exception that 'General tasks' appears at the top. This requires some kind of special handling of the project for sorting purposes. How can I take:

@projects = @company.projects

and sort it so we get a list like this:

'General tasks'
'ABC Project'
'BFA Project'
'ZNS Project'

Answer 1

You can do this in SQL. Here is an example that works in PostgreSQL. Couldn't figure out how to make a database-agnostic version, but you should be able to tailor it to your RDBMS.

@projects = @company.projects.order("name != 'General tasks', name")

!= returns a boolean value, and in PostgreSQL false comes before true so we need NOT EQUAL to make sure the records with the name "General tasks" are shown first. Remaining projects will be sorted by their name.

Another approach would be to add a column to your projects table which flags the "General tasks" projects as special, like:

add_column :projects, :permanent, :boolean, :default => false

Then, you can sort without using raw SQL:

@projects = @company.projects.order(:permanent, :name)

Answer 2

As I answered here, I just released a gem (order_as_specified) that allows you to do native SQL ordering like this:

@company.projects.order_as_specified(name: ['General tasks', 'ABC Project', ...])

It might not be great for your use case if you don't know (or don't want to compute) the full list of options ahead of time. The nice thing is it works with Postgres, MySQL, and SQLite.