In the Rails world, ActiveRecord is the ORM (Object-Relational Mapping) library that connects Ruby objects to database tables. Among its many features, the contains method (though not an official ActiveRecord method but often used as part of conditions) allows developers to retrieve data by specifying if a value is included in an array or a string in a database column. Let’s dive into how contains can simplify querying, make your Rails app more efficient, and keep your code clean and readable.
What is ActiveRecord contains?
ActiveRecord provides an interface for interacting with databases. While contains isn’t an official method, it represents a pattern used in querying arrays and string data. By using contains, developers can perform a search on whether a certain value is part of an array or substring. This can be particularly useful when dealing with data types that store multiple values, like PostgreSQL’s array or JSON fields.
Why Use contains with ActiveRecord?
Using contains in your ActiveRecord queries brings several benefits:
- Efficient Data Retrieval: Instead of looping through records in Ruby,
containslets the database perform the work, reducing data retrieval time. - Readable Code: Writing queries with
containscan make your code more understandable, as it reflects exactly what you’re searching for in your data. - Optimized Performance: Offloading work to the database can improve app performance, especially with large datasets.
How to Use contains with Different Database Types
In Rails, contains querying may differ depending on the database you’re using. Let’s look at some examples for PostgreSQL and MySQL, two popular databases.
PostgreSQL Array Contains Query
If you’re using PostgreSQL, you can work with arrays directly in the database. The @> operator in PostgreSQL allows you to check if an array contains a specific value.
Example: Imagine you have a User model with a column roles, which stores an array of roles for each user.
# Migration for PostgreSQL to add an array column
def change
add_column :users, :roles, :string, array: true, default: []
endTo find users with a specific role, such as admin, you can use:
User.where("roles @> ?", "{admin}")This query tells PostgreSQL to check if the roles array contains the string "admin". PostgreSQL will handle the heavy lifting, making this a highly efficient way to filter users by roles.
Check out our article on ruby multiline strings for a better understanding of how to play with strings.
JSON and JSONB Contains Query in PostgreSQL
For applications where data is stored as JSON or JSONB, the @> operator also works. JSONB fields are useful for handling flexible data, especially for storing key-value pairs without strict schema definitions.
Example: Let’s say you’re storing user preferences in a JSONB column called preferences in the users table. If you want to find users who prefer email notifications, you might structure it like this:
# Sample JSONB data in `preferences`:
# { "notifications": { "email": true, "sms": false } }
User.where("preferences @> ?", { notifications: { email: true } }.to_json)This query filters users by checking if their preferences contain an email notification setting. JSONB queries are a powerful way to work with semi-structured data directly in Rails.
MySQL JSON Contains Query
In MySQL, working with JSON fields differs from PostgreSQL. You can use JSON functions to perform similar searches. MySQL doesn’t have array support like PostgreSQL, but JSON can be a workaround.
Example: Consider you’re storing preferences as JSON. Here’s how you’d query users who want email notifications:
User.where("JSON_CONTAINS(preferences, 'true', '$.notifications.email')")This tells MySQL to check if the JSON field preferences has an email setting set to true. Although less flexible than PostgreSQL arrays, JSON functions provide powerful tools for querying structured data in MySQL.
Using contains with ActiveRecord Enums
Rails Enums are a great way to handle attributes that have a limited set of values. Enums allow you to use a more readable, symbolic approach, while the database stores them as integers.
Example: Suppose you have a status enum in your Order model:
class Order < ApplicationRecord
enum status: { pending: 0, shipped: 1, completed: 2 }
endIf you want to find orders with specific statuses, where with contains can come in handy:
Order.where(status: [:shipped, :completed])This query translates to checking if the status contains shipped or completed. Enums simplify querying by allowing symbolic representation of status values.
Common Scenarios for Using contains in Rails Applications
Understanding how contains can be useful requires practical examples. Here are a few scenarios where contains shines in Rails applications:
1. Filtering Users by Roles
Imagine you’re building a content management system with multiple user roles. Instead of defining a column for each role, you can store roles as an array in a PostgreSQL column:
User.where("roles @> ?", "{editor}")This query checks if the roles array contains editor, making it easy to filter users by their roles.
2. Checking Tags in JSON Arrays
For an e-commerce app, you may have a JSONB field in your Product model to store tags. To find products tagged with eco-friendly, you can use:
Product.where("tags @> ?", '["eco-friendly"]')3. Querying Preferences in JSON Fields
Applications with customizable user settings often store them as JSON. If your app has a User model with a JSON field settings, you can find users with specific preferences.
User.where("settings @> ?", { theme: "dark" }.to_json)This is useful for targeting users with specific preferences, such as those who use a dark theme.
Performance Tips for Using contains in ActiveRecord
Using contains is efficient, but there are best practices to follow for optimal performance.
1. Use Indexes on Array and JSON Columns
If you’re frequently using contains on an array or JSON column, indexing can improve performance. PostgreSQL supports indexes on both array and JSONB columns.
add_index :users, :roles, using: 'gin'
add_index :products, :tags, using: 'gin'2. Avoid Overusing JSONB in Place of Columns
While JSONB is flexible, overusing it can make your schema complex and harder to maintain. Stick to structured columns when possible, reserving JSONB for truly flexible data.
3. Limit JSONB Depth for Faster Queries
When working with nested JSON data, remember that deeper nesting can slow down queries. Design your JSON structure carefully to avoid performance hits.
Real-World Example of ActiveRecord contains in Action
Consider an application managing tasks with an array of labels. Here’s a setup that uses contains in a practical way.
# Migration to add labels as an array to the tasks table
def change
add_column :tasks, :labels, :string, array: true, default: []
endWith this setup, you can easily filter tasks by label:
Task.where("labels @> ?", "{urgent}")This query returns tasks labeled as urgent. By offloading filtering to the database, you reduce application overhead and improve data retrieval speeds.
Best Practices for Writing ActiveRecord Queries with contains
When using contains, keep these tips in mind to write clean, maintainable code.
Use Symbolic Queries: Write queries in a way that reflects the intention of your code. Use symbols and enums wherever possible.
Simplify with Scopes: Define scopes to keep your code DRY (Don’t Repeat Yourself). For example:
class Task < ApplicationRecord
scope :with_label, ->(label) { where("labels @> ?", "{#{label}}") }
endNow you can call Task.with_label('urgent') for cleaner and more readable code.
Test Query Performance: When working with large datasets, test your queries to make sure they don’t slow down your app. Use tools like EXPLAIN in SQL to understand query execution plans.
Wrapping Up
Using contains with ActiveRecord can be a powerful tool for efficient data retrieval and clean, maintainable code. While Rails doesn’t offer a built-in contains method, SQL operators like @> for PostgreSQL or JSON_CONTAINS for MySQL enable this functionality. With proper indexing and thoughtful schema design, contains can make your Rails app faster and easier to work with. Happy querying!
