define.run

• I would like to list the in-stock products first.
• I have a products table with an availability column.
• The valid values for availability column are 'instock' and 'outofstock'.

SELECT * products
ORDER BY array_position(
  array['instock','outofstock'],
  products.availability
)

The above SQL uses the array_position function. It requires two arguments.

• A list of sorted values
• The column that we want to sort.

array_position(sorted_values, column_name)

If you are using #RubyOnRails, that would be the following:

# First turn your custom SQL into an Arel SQL literal
order_sql = Arel.sql("array_position(array['instock','outofstock'], products.availability)")

# Pass the Arel literal to the order method
products = Product.order(order_sql)

What if the column has NULL values?

What I have is scraped data. I found out that there are times when the availability is NULL.

If I just wanted NULL values first or last, I would use NULLS FIRST or NULLS LAST. The SQL would be something like below.

SELECT * products
ORDER BY array_position(array['instock','outofstock'], products.availability) NULLS FIRST

But for products whose availability I am not sure of (NULL value), I want them to be listed on top of products that are out of stock. So I now include the NULL value as the list of values to order by.

SELECT * products
ORDER BY array_position(array['instock',NULL,'outofstock'], products.availability)

An easier way

If the values for a column are going to a finite list of values, this column would benefit from storing the availability as integers, along with using NULLS LAST or 'NULLS FIRST`.

I've been toying with #Postgres for a hobby project and I'm learning quite a lot of nice things. If this is something you are interested in – follow me on @HashNuke

This is what version 1.0 will support.

#worklog