Skip to content

Queries

This page covers the filtering and relation-modification vocabulary that builds on the basics in Relations. Everything here returns a new relation — the original is never mutated.

where shorthands

where(%conditions) accepts several value shapes:

Shape SQL emitted
where({col => $value}) col = ?
where({col => Nil}) col IS NULL
where({col => [a, b, c]}) col IN (?, ?, ?)
where({col => 1..10}) col BETWEEN ? AND ?
where({assoc => $instance}) assoc_id = ? (uses $instance.id) 
1
2
3
4
User.where({age => 18..65});
User.where({email => Nil});
User.where({id => [1, 2, 3]});
User.where({user => $alice});            # belongs_to :user

LIKE predicates

LikePredicate builds a LIKE comparison for substring search. The where key is the column; the predicate carries the pattern.

1
2
3
4
5
6
use ORM::ActiveRecord::Relation::Query::Like;

User.where({fname => LikePredicate.contains('li')});      # fname LIKE '%li%'
User.where({fname => LikePredicate.starts-with('Al')});   # fname LIKE 'Al%'
User.where({fname => LikePredicate.ends-with('ce')});     # fname LIKE '%ce'
User.where.not({fname => LikePredicate.contains('li')});  # fname NOT LIKE '%li%'

% and _ in the value are escaped so they match literally; the clause carries ESCAPE '\', which PostgreSQL, MySQL, and SQLite all accept. It composes with order, limit, scopes, and count like any other condition.

where.not

where.not(%conditions) adds a negated condition. Nil, ranges, arrays, and LikePredicate work the same as the positive form.

1
2
3
4
User.where.not({fname => 'Bob'}).count;
User.where.not({email => Nil}).all;          # IS NOT NULL
User.where.not({age => 18..65}).all;         # NOT BETWEEN
User.where.not({id => [1, 2, 3]}).all;       # NOT IN (...)

JSON / JSONB predicate operators

For :json / :jsonb columns, pass a JsonPredicate as the value to query inside the document. Import it with use ORM::ActiveRecord::Relation::Query::Json;.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# Path extraction (the ->> operator) compared as text — works on PostgreSQL,
# MySQL, and SQLite:
User.where({ prefs => JsonPredicate.extract('theme').eq('dark') });
User.where({ prefs => JsonPredicate.extract('address', 'city').ne('NYC') });

# Containment — PostgreSQL @>, MySQL JSON_CONTAINS (SQLite raises):
User.where({ prefs => JsonPredicate.contains({ theme => 'dark' }) });

# Key existence — PostgreSQL jsonb_exists, MySQL JSON_CONTAINS_PATH (SQLite raises):
User.where({ prefs => JsonPredicate.has-key('theme') });

extract(*@path) walks a nested path and compares the extracted text with .eq / .ne. All three predicates also work under where.not, which negates the whole condition. Containment and key-existence need a :jsonb column on PostgreSQL.

Predicate PostgreSQL MySQL SQLite
extract(path).eq/.ne(v) col -> .. ->> .. col ->> '$.path' col ->> '$.path'
contains(data) col @> ?::jsonb JSON_CONTAINS(col, ?) — (raises)
has-key(key) jsonb_exists(col, ?) JSON_CONTAINS_PATH(col, 'one', ?) — (raises)

where.missing and where.associated

For relations through associations, where.missing(:assoc) finds records with no associated rows; where.associated(:assoc) finds those that have at least one. Both work over belongs_to, has_many, and has_many :through.

1
2
3
User.where.missing(:subscriptions).all;       # users with no subscription
User.where.associated(:subscriptions).all;    # users with at least one
User.where.missing(:magazines).all;           # honors :through

Model.associated(:assoc) is a class-level shortcut for the common case.

excluding

excluding(*@records-or-ids) removes specific rows from the result set, mirroring WHERE id NOT IN (...).

1
2
3
User.excluding($bob).all;                          # by instance
User.excluding($alice, $dave).all;                 # multiple
User.excluding(1, 2, 3).all;                       # by id

It composes:

1
User.where({lname => 'Anderson'}).excluding($alice).all;

or and and

or(other_relation) and and(other_relation) combine WHERE clauses across two relations. The result is a single SQL query with the conditions wrapped in the right boolean structure.

1
2
User.where({fname => 'Alice'}).or(User.where.not({fname => 'Carol'})).all;
User.where({active => True}).and(User.where({verified => True})).all;

merge

merge(other_relation) folds another relation's clauses into this one. Last-wins semantics: other takes precedence for limit, offset, distinct, and readonly. where, order, joins, and similar additive clauses are concatenated.

1
2
3
4
5
6
my $base   = User.where({active => True});
my $sorted = User.order('lname');
my @rows   = $base.merge($sorted).all;

# Override pagination from another relation
User.limit(10).merge(User.limit(2)).all;     # uses LIMIT 2

rewhere

rewhere(%conditions) replaces any prior where conditions on the same columns. Useful when narrowing-then-broadening.

1
2
my $q = User.where({active => True, role => 'admin'});
$q.rewhere({role => 'superadmin'}).all;     # active=True still, role swapped

unscope

unscope(:scope) surgically removes a single relation dimension:

1
2
3
4
User.where({active => True}).unscope(:where).all;   # drop all WHERE
User.order('lname').unscope(:order).all;             # drop ORDER BY
User.group('lname').unscope(:group).all;             # drop GROUP BY
User.distinct.unscope(:distinct).all;                # drop DISTINCT

You can also drop a single where condition by column:

1
2
3
User.where({active => True, role => 'admin'})
    .unscope(where => :role)
    .all;