An executable Ruby script sandbox demonstrating how to implement "tag columns" for a SQLite database.

litearray.rb

# frozen_string_literal: true

# require "bundler/inline"
# 
# gemfile(true) do
#   source "https://rubygems.org"
# 
#   git_source(:github) { |repo| "https://github.com/#{repo}.git" }
# 
#   gem "rails"
#   gem "activerecord"
#   gem "activejob", "~> 7.0.0"
#   gem "sqlite3"
#   # gem "litestack"
# end

require "active_record"
require "active_job"
require "minitest/autorun"
require "logger"

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.json :tags, null: false, default: []
    t.check_constraint "JSON_TYPE(tags) = 'array'", name: 'post_tags_is_array'
  end
end

class ArrayValidator < ActiveModel::EachValidator
  def validate_each(record, attribute, value)
    unless value.is_a? Array
      record.errors.add attribute, (options[:message] || "must be an array")
    end
  end
end

module ArrayColumns
  extend ActiveSupport::Concern
  
  class_methods do
    def array_columns(*column_names)
      @array_columns ||= {}
  
      array_columns_sanitize_list(column_names).each do |column_name|
        @array_columns[column_name] ||= false
      end
  
      @array_columns.each do |column_name, initialized|
        next if initialized
  
        column_name = column_name.to_s
        method_name = column_name.downcase
        
        # JSON_EACH("{table}"."{column}")
        json_each = Arel::Nodes::NamedFunction.new("JSON_EACH", [arel_table[column_name]])
  
        # SELECT DISTINCT value FROM "{table}", JSON_EACH("{table}"."{column}")
        define_singleton_method :"unique_#{method_name}" do |conditions = "true"|
          select('value')
            .from([Post.arel_table, json_each])
            .distinct
            .pluck('value')
            .sort
        end
        
        # SELECT value, COUNT(*) AS count FROM "{table}", JSON_EACH("{table}"."{column}") GROUP BY value ORDER BY value
        define_singleton_method :"#{method_name}_cloud" do |conditions = "true"|
          select('value')
            .from([Post.arel_table, json_each])
            .group('value')
            .order('value')
            .pluck(Arel.sql("value, COUNT(*) AS count"))
            .to_h
        end
        
        # SELECT "{table}".* FROM "{table}" WHERE "{table}"."{column}" IS NOT NULL AND "{table}"."{column}" != '[]'
        scope :"with_#{method_name}", -> {
          where.not(arel_table[column_name].eq(nil))
            .where.not(arel_table[column_name].eq([]))
        }
        
        # SELECT "{table}".* FROM "{table}" WHERE ("{table}"."{column}" IS NULL OR "{table}"."{column}" = '[]')
        scope :"without_#{method_name}", -> {
          where(arel_table[column_name].eq(nil))
            .or(where(arel_table[column_name].eq([])))
        }
        
        # SELECT "{table}".* FROM "{table}" WHERE EXISTS (SELECT 1 FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values}) LIMIT 1)
        scope :"with_any_#{method_name}", ->(*items) {
          values = array_columns_sanitize_list(items)
          overlap = Arel::SelectManager.new(json_each)
            .project(1)
            .where(Arel.sql('value').in(values))
            .take(1)
            .exists
          
          where overlap
        }
        
        # SELECT "{table}".* FROM "{table}" WHERE (SELECT COUNT(*) FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values})) = {values.size};
        scope :"with_all_#{method_name}", ->(*items) {
          values = array_columns_sanitize_list(items)
          count = Arel::SelectManager.new(json_each)
            .project(Arel.star.count)
            .where(Arel.sql('value').in(values))
          contains = Arel::Nodes::Equality.new(count, values.size)
          
          where contains
        }
        
        # SELECT "{table}".* FROM "{table}" WHERE NOT EXISTS (SELECT 1 FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values}) LIMIT 1)
        scope :"without_any_#{method_name}", ->(*items) {
          values = array_columns_sanitize_list(items)
          overlap = Arel::SelectManager.new(json_each)
            .project(1)
            .where(Arel.sql('value').in(values))
            .take(1)
            .exists
          where.not overlap
        }
        
        # SELECT "{table}".* FROM "{table}" WHERE (SELECT COUNT(*) FROM JSON_EACH("{table}"."{column}") WHERE value IN ({values})) != {values.size};
        scope :"without_all_#{method_name}", ->(*items) {
          values = array_columns_sanitize_list(items)
          count = Arel::SelectManager.new(json_each)
            .project(Arel.star.count)
            .where(Arel.sql('value').in(values))
          contains = Arel::Nodes::Equality.new(count, values.size)
          where.not contains
        }
        
        before_validation -> { self[column_name] = self.class.array_columns_sanitize_list(self[column_name]) }
        
        define_method :"has_any_#{method_name}?" do |*values|
          values = self.class.array_columns_sanitize_list(values)
          existing = self.class.array_columns_sanitize_list(self[column_name])
          (values & existing).present?
        end
        
        define_method :"has_all_#{method_name}?" do |*values|
          values = self.class.array_columns_sanitize_list(values)
          existing = self.class.array_columns_sanitize_list(self[column_name])
          (values & existing).size == values.size
        end
        
        alias_method :"has_#{method_name.singularize}?", :"has_all_#{method_name}?"
        
        @array_columns[column_name] = true
      end
    end
    
    def array_columns_sanitize_list(values = [])
      return [] if values.nil?
      values.select(&:present?).map(&:to_s).uniq.sort
    end
  end
end

class Post < ActiveRecord::Base
  include ArrayColumns
  
  has_many :comments
  
  validates :tags, array: true
  
  array_columns :tags
end

# -----------------------------------------------------------------------------

POST_1 = Post.create!(tags: %w[a b c d])
POST_2 = Post.create!(tags: %w[c d e f])
POST_3 = Post.create!

class TagColumnsTest < Minitest::Test
  def test_database_adapter
    assert_equal 'sqlite3', ActiveRecord::Base.connection_db_config.adapter
  end
  
  def test_unique_tags
    assert_equal %w[a b c d e f], Post.unique_tags
  end
  
  def test_tags_cloud
    assert_equal({ 'a' => 1, 'b' => 1, 'c' => 2, 'd' => 2, 'e' => 1, 'f' => 1 }, Post.tags_cloud)
  end
  
  def test_with_tags
    collection = Post.with_tags
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_without_tags
    collection = Post.without_tags
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_unique_argument
    collection = Post.with_any_tags 'a'
    assert collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_shared_argument
    collection = Post.with_any_tags 'c'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_nonexistent_argument
    collection = Post.with_any_tags 'z'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_unique_arguments
    collection = Post.with_any_tags 'a', 'b'
    assert collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_shared_arguments
    collection = Post.with_any_tags 'c', 'd'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_nonexistent_arguments
    collection = Post.with_any_tags 'y', 'z'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_any_tags_receiving_split_arguments
    collection = Post.with_any_tags 'a', 'f'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_unique_argument
    collection = Post.with_all_tags 'a'
    assert collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_shared_argument
    collection = Post.with_all_tags 'c'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_nonexistent_argument
    collection = Post.with_all_tags 'z'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_unique_arguments
    collection = Post.with_all_tags 'a', 'b'
    assert collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_shared_arguments
    collection = Post.with_all_tags 'c', 'd'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_nonexistent_arguments
    collection = Post.with_all_tags 'y', 'z'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_with_all_tags_receiving_split_arguments
    collection = Post.with_all_tags 'a', 'f'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert !collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_unique_argument
    collection = Post.without_any_tags 'a'
    assert !collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_shared_argument
    collection = Post.without_any_tags 'c'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_nonexistent_argument
    collection = Post.without_any_tags 'z'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_unique_arguments
    collection = Post.without_any_tags 'a', 'b'
    assert !collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_shared_arguments
    collection = Post.without_any_tags 'c', 'd'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_nonexistent_arguments
    collection = Post.without_any_tags 'y', 'z'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_any_tags_receiving_split_arguments
    collection = Post.without_any_tags 'a', 'f'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_unique_argument
    collection = Post.without_all_tags 'a'
    assert !collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_shared_argument
    collection = Post.without_all_tags 'c'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_nonexistent_argument
    collection = Post.without_all_tags 'z'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_unique_arguments
    collection = Post.without_all_tags 'a', 'b'
    assert !collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_shared_arguments
    collection = Post.without_all_tags 'c', 'd'
    assert !collection.include?(POST_1)
    assert !collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_nonexistent_arguments
    collection = Post.without_all_tags 'y', 'z'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_without_all_tags_receiving_split_arguments
    collection = Post.without_all_tags 'a', 'f'
    assert collection.include?(POST_1)
    assert collection.include?(POST_2)
    assert collection.include?(POST_3)
  end
  
  def test_has_any_tags_receiving_unique_argument
    assert POST_1.has_any_tags?('a')
    assert !POST_2.has_any_tags?('a')
    assert !POST_3.has_any_tags?('a')
  end
  
  def test_has_any_tags_receiving_shared_argument
    assert POST_1.has_any_tags?('c')
    assert POST_2.has_any_tags?('c')
    assert !POST_3.has_any_tags?('c')
  end
  
  def test_has_any_tags_receiving_nonexistent_argument
    assert !POST_1.has_any_tags?('z')
    assert !POST_2.has_any_tags?('z')
    assert !POST_3.has_any_tags?('z')
  end
  
  def test_has_any_tags_receiving_unique_arguments
    assert POST_1.has_any_tags?('a', 'b')
    assert !POST_2.has_any_tags?('a', 'b')
    assert !POST_3.has_any_tags?('a', 'b')
  end
  
  def test_has_any_tags_receiving_shared_arguments
    assert POST_1.has_any_tags?('c', 'd')
    assert POST_2.has_any_tags?('c', 'd')
    assert !POST_3.has_any_tags?('c', 'd')
  end
  
  def test_has_any_tags_receiving_nonexistent_arguments
    assert !POST_1.has_any_tags?('y', 'z')
    assert !POST_2.has_any_tags?('y', 'z')
    assert !POST_3.has_any_tags?('y', 'z')
  end
  
  def test_has_any_tags_receiving_split_arguments
    assert POST_1.has_any_tags?('a', 'f')
    assert POST_2.has_any_tags?('a', 'f')
    assert !POST_3.has_any_tags?('a', 'f')
  end
  
  def test_has_all_tags_receiving_unique_argument
    assert POST_1.has_all_tags?('a')
    assert !POST_2.has_all_tags?('a')
    assert !POST_3.has_all_tags?('a')
  end
  
  def test_has_all_tags_receiving_shared_argument
    assert POST_1.has_all_tags?('c')
    assert POST_2.has_all_tags?('c')
    assert !POST_3.has_all_tags?('c')
  end
  
  def test_has_all_tags_receiving_nonexistent_argument
    assert !POST_1.has_all_tags?('z')
    assert !POST_2.has_all_tags?('z')
    assert !POST_3.has_all_tags?('z')
  end
  
  def test_has_all_tags_receiving_unique_arguments
    assert POST_1.has_all_tags?('a', 'b')
    assert !POST_2.has_all_tags?('a', 'b')
    assert !POST_3.has_all_tags?('a', 'b')
  end
  
  def test_has_all_tags_receiving_shared_arguments
    assert POST_1.has_all_tags?('c', 'd')
    assert POST_2.has_all_tags?('c', 'd')
    assert !POST_3.has_all_tags?('c', 'd')
  end
  
  def test_has_all_tags_receiving_nonexistent_arguments
    assert !POST_1.has_all_tags?('y', 'z')
    assert !POST_2.has_all_tags?('y', 'z')
    assert !POST_3.has_all_tags?('y', 'z')
  end
  
  def test_has_all_tags_receiving_split_arguments
    assert !POST_1.has_all_tags?('a', 'f')
    assert !POST_2.has_all_tags?('a', 'f')
    assert !POST_3.has_all_tags?('a', 'f')
  end
end

# class BuggyJob < ActiveJob::Base
#   def perform
#     puts "performed"
#   end
# end
# 
# class BuggyJobTest < ActiveJob::TestCase
#   def test_stuff
#     assert_enqueued_with(job: BuggyJob) do
#       BuggyJob.perform_later
#     end
#   end
# end