DEV Community

Abdelkader Boudih
Abdelkader Boudih

Posted on

2

NoFlyList: How NoFlyList Optimizes Tag Queries

Database-Specific Strategies

NoFlyList automatically detects your database type and uses optimized queries:

class Product < ApplicationRecord
  include NoFlyList::TaggableRecord
  has_tags :categories
end

# This generates different SQL for each database
Product.with_any_categories("electronics", "gaming")
Enter fullscreen mode Exit fullscreen mode

PostgreSQL Optimization

PostgreSQL query:

# Using array operators and CTE for better performance
SELECT "products".*
FROM "products"
WHERE "products"."id" IN (
  SELECT DISTINCT "products"."id"
  FROM products
  INNER JOIN "product_taggings" ON "product_taggings"."taggable_id" = "products"."id"
  INNER JOIN "product_tags" ON "product_tags"."id" = "product_taggings"."tag_id"
  WHERE "product_taggings"."context" = 'category'
  AND "product_tags"."name" = ANY(ARRAY['electronics', 'gaming'])
)
Enter fullscreen mode Exit fullscreen mode

MySQL Optimization

MySQL query:

# Using FIND_IN_SET and subqueries
SELECT `products`.*
FROM `products`
WHERE `products`.`id` IN (
  SELECT `products`.`id`
  FROM products
  INNER JOIN `product_taggings` ON `product_taggings`.`taggable_id` = `products`.`id`
  INNER JOIN `product_tags` ON `product_tags`.`id` = `product_taggings`.`tag_id`
  WHERE `product_taggings`.`context` = 'category'
  AND `product_tags`.`name` IN ('electronics', 'gaming')
  GROUP BY `products`.`id`
)
Enter fullscreen mode Exit fullscreen mode

SQLite Optimization

SQLite query:

# Optimized for SQLite's simpler query planner
SELECT "products".*
FROM "products"
WHERE "products"."id" IN (
  SELECT "products"."id"
  FROM products
  INNER JOIN product_taggings ON product_taggings.taggable_id = products.id
  INNER JOIN product_tags ON product_tags.id = product_taggings.tag_id
  WHERE product_taggings.context = 'category'
  AND product_tags.name IN ('electronics', 'gaming')
)
Enter fullscreen mode Exit fullscreen mode

Complex Queries

# Finding products with ALL specified tags
Product.with_all_categories("electronics", "gaming")

# PostgreSQL uses:
SELECT "products".*
FROM "products"
WHERE "products"."id" IN (
  SELECT "products"."id"
  FROM products
  INNER JOIN "product_taggings" ON "product_taggings"."taggable_id" = "products"."id"
  INNER JOIN "product_tags" ON "product_tags"."id" = "product_taggings"."tag_id"
  WHERE "product_taggings"."context" = 'category'
  AND "product_tags"."name" IN ('electronics', 'gaming')
  GROUP BY "products"."id"
  HAVING COUNT(DISTINCT "product_tags"."name") = 2
)

# Finding products without specific tags
Product.without_any_categories("discontinued")

# Finding products with exact tag set
Product.with_exact_categories(["electronics", "gaming"])
Enter fullscreen mode Exit fullscreen mode

Performance Tips

  1. Index Optimization:
class CreateProductTags < ActiveRecord::Migration[7.2]
  def change
    add_index :product_tags, :name
    add_index :product_taggings, [:taggable_id, :taggable_type, :context]
  end
end
Enter fullscreen mode Exit fullscreen mode

Unlike AATO, the gem support multiple database connections and mixed adapters.

  1. Counter Cache:
class Product < ApplicationRecord
  has_tags :categories, counter_cache: true
end
Enter fullscreen mode Exit fullscreen mode
  1. Eager Loading:
# Efficient loading of products with their tags
Product.includes(:categories)
       .with_any_categories("electronics")
Enter fullscreen mode Exit fullscreen mode

Debugging Queries

Use query logging to see optimizations:

# config/environments/development.rb
config.active_record.verbose_query_logs = true

# In console
Product.with_any_categories("electronics").explain
Enter fullscreen mode Exit fullscreen mode

Common Patterns

  1. Category Trees:
Product.with_all_categories("electronics")
       .with_any_categories("gaming", "professional")
Enter fullscreen mode Exit fullscreen mode
  1. Exclusions:
Product.with_any_categories("electronics")
       .without_any_categories("discontinued", "clearance")
Enter fullscreen mode Exit fullscreen mode
  1. Exact Matching:
Product.with_exact_categories(["gaming", "electronics"])
Enter fullscreen mode Exit fullscreen mode

Each pattern generates optimized SQL based on your database.
If you know a better query, feel free to open a pull request on the adapter query.

Sentry workshop image

Flaky tests got you down?

Learn how to merge your code without having to hit “rerun” every 5 minutes 😮‍💨

Save your spot now.

Top comments (0)

Billboard image

Create up to 10 Postgres Databases on Neon's free plan.

If you're starting a new project, Neon has got your databases covered. No credit cards. No trials. No getting in your way.

Try Neon for Free →

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay