How to properly query the array datatype

Some Background (You can skip this)

So as a fun passion project I've been working an application to manage my growing collection of Magic: The Gathering cards. My hope is that it will speed up deck building when I can quickly see what cards are in my collection along with keeping track of what decks I have them in.

For those that don't know what Magic: The Gathering (MTG) is, just know that it's the world's best (My opinion) card game with endless possibilities for fun. It's also a bottomless money pit that has consumed more of my money than I'd like to think about...

The Problem

I want to be able to query for varying card colors known as color identity. The colors can be White, Blue, Black, Red, Green, or None(Colorless). They can also be any combination of colors, or no colors if the card is colorless.

I've decided to use an Array datatype for the card colors.

create_table :cards do |t|
  t.string 'name'
  t.string 'colors', array: true
end

# Card.create name: "Supreme Verdict", colors: ["white", "blue"]

The types of queries I want to make:

1. Find a specific color
2. Find a specific color combo
3. Find cards that have more than one color
4. Find cards that have no color
5. Find one or another color.

The Solution

Thankfully Ruby on Rails guides provides the solutions to questions 1, 2, and 3!

Find a specific color

Card.where("'blue' = ANY (colors)") will return any card that has 'blue' within colors. Note that this will also return cards that have multiple colors, one of them being blue.

We can use scopes and lambdas to make this query more dynamic!

Within app/models/card.rb add:

scope :single_color, -> (color) { where("'#{color}' = ANY (colors)") }

Now we can use Card.single_color("blue") to get all blue cards!

Find a specific color combo

Card.where("colors @> ARRAY[?]::varchar[]", ["blue", "white"]) will return any cards that have exactly blue and white within their colors.

Using scopes within app/models/card.rb add:

scope :combo_color, -> (card_colors) { where("colors @> ARRAY[?]::varchar[]", card_colors) }

Here we can use an array as a param. Card.combo_color(['blue', 'white']) will return all cards that have blue and white within their colors.

Find cards that have more than one color

where('array_length(colors, 1) > 1') will return all cards that have more than one color. Turning this into a scope is relatively easy since we won't need to pass a variable.

scope :multi_color, -> { where('array_length(colors, 1) > 1') }

You can change > 1 to make any comparison when looking for arrays that are greater/less than a specific length.

Find cards that have no color

where(colors: []) will return any cards that have an empty array. This was a very straightforward query.

You could also change the multi_color scope above to look for any arrays with a length of 0 as well!

Find one or another color

Now this one was a lot more tricky than the others. I want to be able to search for cards that have one OR another color. We know that in order to do that we must use or.

For example Card.where("'red' = ANY (colors)").or(Card.where("'green' = ANY (colors)") would return all cards that contain red and all cards that contain green. but how do we do this dynamically? We wouldn't want to create a scope for every possible combination.

Eventually, I came across another article titled Rails: Dynamically Chain Scopes to Clean up SQL Queries. It's a great guide on how to use the inject method to dynamically chain scopes onto queries.

Inject iterates over an array of values and accepts an initial value. For example, we can use it to add an array of numbers together.

[4, 2, 7, 4, 9].inject(0) { |initial, value| initial + value }
# Output: 26

Now, all we have to do is use the single_color scope from above, and apply the inject method to the colors we want.

For example let's say we want to build a deck that contains white, blue, and black cards.

colors = ["white", "blue", "black"]

colors[1..-1].inject(single_color(colors.first)) {|initial, color| initial.or(Card.single_color(color)) }

# Output single_color('white').or(Card.single_color('blue')).or(Card.single_color('black'))

Conclusion

The array datatype is a great way to hold information that can contain one or more items. Hopefully, these query examples will help you gain a better grasp, or maybe convince you to use arrays in your database.

Thanks for reading!