define.run

Notes on building things

  • 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

Making a page out of my favourite twitter threads and links from them. Now I won't have to dig up my Twitter bookmarks everytime :D

Read more...

~ This is a rabbit hole ~

Read more...

Books to read for the next five years (from 2020)

Read more...

— This post is a work in progress. These are my notes from reading the Traction book

Read more...

Read more...

Sometime ago I bought this app called Core Data Lab. The app helps inspect CoreData stores created by my mac apps. This is very handy when building Mac apps (probably iOS apps too – I haven't tried it).

Sharing notes here because it took me a while to figure out how to get the app setup and running for my project – I'm just getting started with building apps with Swift.

Read more...

This is what version 1.0 will support.

JIRA app features

#worklog

or “How to POST a form in Swift using URLSession”

These examples will be using httpbin.org to test our request.

Read more...

aka “How to decode response from a JSON API”

Read more...

Enter your email to subscribe to updates.