Find a common list of queries we at Harled have used in our Ruby on Rails Platforms

LocalQueryPerformance.md

Query Performance Monitoring

We've had the case where a seemingly small change in a query (such as adding a .join() clause) actually caused us a large load-related headache when run multiple times by our users in production. To avoid this issue in future iterations we now use (rack-mini-profiler)[https://github.com/MiniProfiler/rack-mini-profiler] to help us measure our queries in development! The profiler will give you a file-by-file, query-by-query analysis of your page load times and flag expensive queries.

MatchingRecordOrderToArray.md

Return records in specified order

Ordering with rails is, well, nice!

@records.order(created_at: :asc)

Is a great example of the Active Record Query Interface at work! But what if we need that little bit more?

Sometimes it’s impossible to achieve an unconventional order with your current setup through database queries directly. Additionally, to query directly will usually require an unreasonable amount of time to refactor and migrate data. So, enter https://github.com/panorama-ed/order_as_specified, a gem designed to return records exactly as laid out in a custom built array.

An example we ran into for our platform is based on user preferences, prioritize records with the users’ squadron first and order the rest of the records by squadron alphabetically. To accomplish this, we do the following:

@records.order_as_specified(squadron_id: @records.pluck(:squadron_id).unshift(@user.squadron_id).uniq)

PatternMatching.md

Pattern Matching

We use pattern matching a couple times in our code to search records that contain a specific pattern (such as searching for user names and emails). To accomplish this, usually you would use clauses like ILIKE, LIKE, SIMILAR etc (see postgress pattern matching), but we can use a gem called pg_search to accomplish this in a much more concise way. pg_search combines pattern matching with text search dictionaries to simplify searching with Postgres and leverage all of it’s powerful text searching tools. To highlight some cool stuff this gem can do, this is an snippet taken from the pg_search release (see post here):

That way, we could improve our Book.search_title scope by using unaccent to ignore accent marks, Double Metaphone to match soundalikes, and trigrams to match misspellings.

So, for example, adding a model scope on your user model could look something like this:

pg_search_scope :search_all, against: {
    last_name: "A",
    first_name: "B",
    email: "C"
  },
  using: {tsearch: {prefix: true}}, ignoring: :accents

Which allows us to search any of the last_name, first_name and email columns while ignoring accents in one clause like so: User.search_all("Bob").

SelectingDistinctRecords.md

Selecting distinct records across various fields

This query combines a few clauses which finds one distinct record given any amount of fields in the database. Additionally, we will vary the selection of those records based on an order clause, allowing us to pass in any column as the order target.

First: The DISTINCT clause

This clause tells the database to do grab unique values from the database based on a given field. In Rails, we wrap it in a .select() query to mimic the SQL SELECT DISTINCT query. For example, to select records that are distinct on the user_id column, we could use this:

.select("DISTINCT ON (user_id) *")

There are two parts here: DISTINCT ON (X) Y. The X represents the field(s) to filter on for uniqueness. The Y represents a list of columns to include in the query, where Y = * is all columns.

Second: The ORDER clause

So the above query is great, but it’s ultimately the same as using Rails’ built in .distinct clause. Where it becomes more useful is when you need to find uniqueness based on more than one column and specify how to select the unique record by ordering the records.

The DISTINCT ON query will take the first of the list of records, so we can make it smarter by specifying which column to order by. For example, to select the most recent record based on the created_atcolumn, your query could look like this:

.select("DISTINCT ON (user_id) *").order(:user_id, created_at: :desc)

And many more

I will be updating this doc as I find new and interesting queries that we use in Rails on our day to day.