Queries

Learn how to find, filter, and retrieve data with IlanaORM

What is Querying?

Querying is how you ask your database for specific information. Instead of getting all records, you can filter, sort, and limit results to get exactly what you need.

Real-world analogy: Querying is like using filters on a shopping website. Instead of seeing all products, you filter by category, price range, brand, etc., to find exactly what you want.

Basic Queries

Getting All Records

// Get all users
const users = await User.all();

// Get all active users (using a scope)
const activeUsers = await User.active().get();

Finding Single Records

// Find by primary key
const user = await User.find(1);

// Find by primary key or throw error
const user = await User.findOrFail(1);

// Find by any column
const user = await User.findBy('email', '[e-mail]');

// Get first record
const user = await User.first();

// Get first record or throw error
const user = await User.firstOrFail();

When to use what?

• find() - When you have the ID and it’s okay if the record doesn’t exist
• findOrFail() - When you have the ID and the record must exist
• findBy() - When searching by a specific column value
• first() - When you want the first record from a query

The Query Builder

The query builder lets you chain methods to build complex queries:

const users = await User.query()
  .where('is_active', true)
  .where('age', '>', 18)
  .orderBy('name')
  .limit(10)
  .get();

How chaining works: Each method returns a query builder object, so you can keep adding more conditions. Think of it like building a sentence: “Get users WHERE active is true AND age is greater than 18, ORDER BY name, LIMIT to 10.”

Where Clauses

Basic Where Conditions

// Simple equality
const users = await User.query()
  .where('is_active', true)
  .get();

// With operators
const adults = await User.query()
  .where('age', '>', 18)
  .where('age', '<=', 65)
  .get();

// Multiple conditions (AND)
const premiumUsers = await User.query()
  .where('is_active', true)
  .where('subscription', 'premium')
  .where('credits', '>', 0)
  .get();

Available Operators

Operator	Description	Example
=	Equal (default)	where('age', 25)
>	Greater than	where('age', '>', 18)
>=	Greater than or equal	where('age', '>=', 21)
<	Less than	where('price', '<', 100)
<=	Less than or equal	where('price', '<=', 50)
!= or <>	Not equal	where('status', '!=', 'banned')
like	Pattern matching	where('name', 'like', '%john%')
ilike	Case-insensitive like	where('name', 'ilike', '%JOHN%')

OR Conditions

// Users who are admin OR moderator
const staff = await User.query()
  .where('role', 'admin')
  .orWhere('role', 'moderator')
  .get();

// Complex OR conditions
const eligibleUsers = await User.query()
  .where('is_active', true)
  .where((query) => {
    query.where('age', '>', 18)
         .orWhere('has_parent_permission', true);
  })
  .get();

Where In / Not In

// Users with specific roles
const staff = await User.query()
  .whereIn('role', ['admin', 'moderator', 'editor'])
  .get();

// Users without specific roles
const regularUsers = await User.query()
  .whereNotIn('role', ['admin', 'moderator'])
  .get();

// Using arrays of IDs
const specificUsers = await User.query()
  .whereIn('id', [1, 5, 10, 15])
  .get();

Null Checks

// Users with verified emails
const verifiedUsers = await User.query()
  .whereNotNull('email_verified_at')
  .get();

// Users without profile pictures
const usersWithoutAvatars = await User.query()
  .whereNull('avatar')
  .get();

// OR variants
const users = await User.query()
  .whereNull('deleted_at')
  .orWhereNull('suspended_at')
  .orWhereNotNull('banned_at')
  .get();

Between Values

// Users aged between 18 and 65
const workingAgeUsers = await User.query()
  .whereBetween('age', [18, 65])
  .get();

// Exclude a range
const nonMinors = await User.query()
  .whereNotBetween('age', [0, 17])
  .get();

Date Queries

// Posts created today
const todaysPosts = await Post.query()
  .whereDate('created_at', new Date())
  .get();

// Posts from December 2023
const decemberPosts = await Post.query()
  .whereMonth('created_at', 12)
  .whereYear('created_at', 2023)
  .get();

// Posts created on the 15th of any month (all databases)
const midMonthPosts = await Post.query()
  .whereDay('created_at', 15)
  .get();

// Posts created after noon (all databases)
const afternoonPosts = await Post.query()
  .whereTime('created_at', '>', '12:00:00')
  .get();

whereDate, whereDay, whereMonth, whereYear, and whereTime all work across MySQL, PostgreSQL, and SQLite.

JSON Queries

For databases that support JSON (PostgreSQL, MySQL):

// Users with dark theme preference
const darkThemeUsers = await User.query()
  .whereJsonContains('preferences', { theme: 'dark' })
  .get();

// Users with more than 3 tags
const taggedUsers = await User.query()
  .whereJsonLength('tags', '>', 3)
  .get();

// Users with specific nested JSON value
const notificationUsers = await User.query()
  .whereJsonContains('settings->notifications->email', true)
  .get();

Ordering Results

// Order by single column
const users = await User.query()
  .orderBy('name')
  .get();

// Order by multiple columns
const users = await User.query()
  .orderBy('role')
  .orderBy('name', 'desc')
  .get();

// Random order
const randomUsers = await User.query()
  .inRandomOrder()
  .limit(5)
  .get();

// Order by raw SQL
const users = await User.query()
  .orderByRaw('CASE WHEN role = "admin" THEN 1 ELSE 2 END')
  .get();

Limiting and Pagination

Basic Limiting

// Get first 10 users
const users = await User.query()
  .limit(10)
  .get();

// Skip first 20, then get 10 (pagination)
const users = await User.query()
  .offset(20)
  .limit(10)
  .get();

// Get users 21-30
const users = await User.query()
  .skip(20)
  .take(10)
  .get();

// Page helper — equivalent to offset((page-1) * perPage).limit(perPage)
const users = await User.query()
  .forPage(3, 15) // page 3, 15 per page
  .get();

Built-in Pagination

// Standard pagination
const users = await User.query()
  .where('is_active', true)
  .paginate(1, 15); // page 1, 15 per page

console.log(users.data); // Array of users
console.log(users.total); // Total count
console.log(users.currentPage); // 1
console.log(users.perPage); // 15
console.log(users.lastPage); // Last page number

// Simple pagination (no total count)
const users = await User.query()
  .where('is_active', true)
  .simplePaginate(1, 15);

Aggregates

Get summary information about your data:

// Count records
const userCount = await User.query().count();
const activeUserCount = await User.query()
  .where('is_active', true)
  .count();

// Sum values
const totalCredits = await User.query().sum('credits');

// Average values
const averageAge = await User.query().avg('age');

// Min/Max values
const youngestAge = await User.query().min('age');
const oldestAge = await User.query().max('age');

// Multiple aggregates
const stats = await User.query()
  .select('role')
  .selectRaw('COUNT(*) as count')
  .selectRaw('AVG(age) as average_age')
  .groupBy('role')
  .get();

Grouping and Having

// Group users by role
const roleStats = await User.query()
  .select('role')
  .selectRaw('COUNT(*) as count')
  .groupBy('role')
  .get();

// Only show roles with more than 5 users
const popularRoles = await User.query()
  .select('role')
  .selectRaw('COUNT(*) as count')
  .groupBy('role')
  .having('count', '>', 5)
  .get();

// Complex grouping
const monthlyStats = await Post.query()
  .selectRaw('YEAR(created_at) as year')
  .selectRaw('MONTH(created_at) as month')
  .selectRaw('COUNT(*) as post_count')
  .selectRaw('AVG(views) as avg_views')
  .groupBy('year', 'month')
  .orderBy('year', 'desc')
  .orderBy('month', 'desc')
  .get();

Joins

Combine data from multiple tables:

// Inner join
const postsWithAuthors = await Post.query()
  .join('users', 'posts.user_id', 'users.id')
  .select('posts.*', 'users.name as author_name')
  .get();

// Left join (includes posts without authors)
const allPosts = await Post.query()
  .leftJoin('users', 'posts.user_id', 'users.id')
  .select('posts.*', 'users.name as author_name')
  .get();

// Multiple joins
const postsWithDetails = await Post.query()
  .join('users', 'posts.user_id', 'users.id')
  .join('categories', 'posts.category_id', 'categories.id')
  .select(
    'posts.*',
    'users.name as author_name',
    'categories.name as category_name'
  )
  .get();

When to use joins vs relationships: Use joins when you need specific data from related tables in a single query. Use relationships (covered in the next section) when you want to work with related models as objects.

Raw Queries

Sometimes you need to write custom SQL:

// Raw where conditions
const users = await User.query()
  .whereRaw('age > ? AND credits < ?', [25, 100])
  .get();

// Raw select
const users = await User.query()
  .selectRaw('*, YEAR(created_at) as registration_year')
  .get();

// Raw order by
const users = await User.query()
  .orderByRaw('CASE WHEN role = "admin" THEN 1 ELSE 2 END')
  .get();

// Completely raw query
const results = await User.query()
  .raw('SELECT role, COUNT(*) as count FROM users GROUP BY role');

Security Note: Always use parameter binding (?) in raw queries to prevent SQL injection attacks. Never concatenate user input directly into SQL strings.

Advanced Subqueries

Subquery in addSelect

Add a computed column from a subquery without a join:

// Add each user's latest post title as a column
const users = await User.query()
  .addSelect({
    latest_post_title: (sub) => sub
      .from('posts')
      .select('title')
      .whereRaw('posts.user_id = users.id')
      .orderBy('created_at', 'desc')
      .limit(1),
  })
  .get();

users[0].latest_post_title; // "My latest article"

Order by a subquery

Sort results by a value computed from another table:

// Order users by the number of posts they have
const users = await User.query()
  .orderBySubquery((sub) => {
    sub.from('posts').count('id').whereRaw('posts.user_id = users.id');
  }, 'desc')
  .get();

---

Pending Attributes on Scopes

When you use a scoped query to create records, withPendingAttributes() lets the scope automatically set default column values on any model created through it:

// In a Post model
static scopePublished(query) {
  return query
    .where('status', 'published')
    .withPendingAttributes({ status: 'published' });
}

// Creating through the scope automatically sets status = 'published'
const post = await Post.query().published().new({ title: 'Hello World' });
// post.status === 'published'  (set by the scope, not the caller)
await post.save();

You can also call create() on the scoped query:

const post = await Post.query().published().create({ title: 'Hello World' });
// Inserts with status = 'published'

---

Column Expressions with F()

Use F() to reference a column’s current value in an update — no raw SQL, no race conditions, no need to fetch the record first:

import { F } from 'ilana-orm';

// Increment views without fetching the post
await Post.query().where('id', postId).update({ views: F('views').plus(1) });

// Decrement stock
await Product.query().where('id', id).update({ stock: F('stock').minus(quantity) });

Available operations: .plus(n), .minus(n), .times(n), .divide(n).

F() generates a safe parameterized expression — it never interpolates values directly into SQL.

---

Plain Objects with values()

get() always returns model instances. Use values() when you only need the raw data and want to skip model hydration — faster for read-heavy endpoints:

// Returns plain objects instead of model instances
const users = await User.query()
  .select('id', 'name', 'email')
  .where('is_active', true)
  .values();

// [{ id: 1, name: 'John', email: '[e-mail]' }, ...]
console.log(users[0].name); // 'John' — plain object, no ORM overhead

---

Strict Fetching and Debugging

sole() — exactly one or throw

sole() is like firstOrFail() but also throws if more than one record matches. Use it when your query should always return exactly one row:

// Throws ModelNotFoundException if no match, Error if multiple matches
const config = await AppConfig.query()
  .where('key', 'site_title')
  .sole();

// Good for unique constraints — catches duplicates early
const user = await User.query()
  .where('email', req.body.email)
  .sole(); // alerts you if email uniqueness is broken

tap() — debug without breaking the chain

tap() runs a callback for logging or inspection without affecting the query:

const users = await User.query()
  .where('is_active', true)
  .tap((query) => console.log('SQL:', query.toSql()))
  .orderBy('name')
  .get();

// Or inspect mid-chain during development
const results = await Post.query()
  .published()
  .tap((q) => console.log('Before pagination:', q.toSql()))
  .paginate(1, 10);

---

Conditional Queries

Build queries based on conditions:

async function getUsers(filters = {}) {
  let query = User.query();
  
  // Traditional approach
  if (filters.role) {
    query = query.where('role', filters.role);
  }
  
  if (filters.isActive !== undefined) {
    query = query.where('is_active', filters.isActive);
  }
  
  if (filters.minAge) {
    query = query.where('age', '>=', filters.minAge);
  }
  
  return await query.get();
}

// Using when() method (cleaner)
async function getUsersWithWhen(filters = {}) {
  return await User.query()
    .when(filters.role, (query, role) => {
      query.where('role', role);
    })
    .when(filters.isActive !== undefined, (query) => {
      query.where('is_active', filters.isActive);
    })
    .when(filters.minAge, (query, minAge) => {
      query.where('age', '>=', minAge);
    })
    .get();
}

Exists Queries

Relation existence (recommended)

Use whereHas / doesntHave to filter by whether a named relationship exists. These build a WHERE EXISTS subquery automatically.

The related model must be registered in ModelRegistry (call static { this.register(); } in its class body) so the ORM can resolve its table name at query-build time.

// Users who have at least one post
const authors = await User.query().has('posts').get();

// Users who have more than 5 posts
const prolific = await User.query().has('posts', '>', 5).get();

// Users who have at least one published post (with a constraint)
const publishedAuthors = await User.query()
  .whereHas('posts', (query) => {
    query.where('is_published', true);
  })
  .get();

// Users with no posts at all
const lurkers = await User.query().doesntHave('posts').get();
// Also available as:
const lurkers2 = await User.query().whereDoesntHave('posts').get();

// Check if any record exists
const hasAdmins = await User.query().where('role', 'admin').exists();
const noAdmins  = await User.query().where('role', 'admin').doesntExist();

has(relation, operator?, count?) is the simple form — filter by relation existence with an optional count. Use whereHas when you need to add constraints on the related records themselves.

Raw subquery existence

// Manual WHERE EXISTS (use when you need full control over the subquery)
const authorsWithPosts = await User.query()
  .whereExists((query) => {
    query.select('*')
         .from('posts')
         .whereRaw('posts.user_id = users.id');
  })
  .get();

Query Scopes

Reusable query logic (defined in your model):

// In your Post model
class Post extends Model {
  static scopePublished(query) {
    return query.where('is_published', true);
  }
  
  static scopePopular(query, threshold = 100) {
    return query.where('views', '>', threshold);
  }
  
  static scopeRecent(query, days = 7) {
    const date = new Date();
    date.setDate(date.getDate() - days);
    return query.where('created_at', '>', date);
  }
}

// Usage
const posts = await Post.query()
  .published()
  .popular(500)
  .recent(30)
  .orderBy('views', 'desc')
  .get();

Performance Tips

1. Use Specific Selects

// ❌ Gets all columns (slower)
const users = await User.all();

// ✅ Gets only needed columns (faster)
const users = await User.query()
  .select('id', 'name', 'email')
  .get();

2. Use Limits

// ❌ Gets all records (could be millions)
const users = await User.query().where('is_active', true).get();

// ✅ Limits results
const users = await User.query()
  .where('is_active', true)
  .limit(100)
  .get();

3. Use Indexes

Make sure your database has indexes on columns you query frequently:

// If you often query by email, make sure there's an index
const user = await User.findBy('email', '[e-mail]');

// If you often filter by role and status, consider a compound index
const users = await User.query()
  .where('role', 'admin')
  .where('is_active', true)
  .get();

4. Use Eager Loading for Relationships

// ❌ N+1 problem (many queries)
const users = await User.all();
for (const user of users) {
  console.log(user.posts); // Each access triggers a query
}

// ✅ Eager loading (single query)
const users = await User.with('posts').get();
for (const user of users) {
  console.log(user.posts); // No additional queries
}

Common Query Patterns

Search Functionality

async function searchUsers(searchTerm) {
  return await User.query()
    .where((query) => {
      query.where('name', 'like', `%${searchTerm}%`)
           .orWhere('email', 'like', `%${searchTerm}%`)
           .orWhere('username', 'like', `%${searchTerm}%`);
    })
    .where('is_active', true)
    .orderBy('name')
    .limit(50)
    .get();
}

Dashboard Statistics

async function getDashboardStats() {
  const [
    totalUsers,
    activeUsers,
    newUsersToday,
    topUsers
  ] = await Promise.all([
    User.query().count(),
    User.query().where('is_active', true).count(),
    User.query().whereDate('created_at', new Date()).count(),
    User.query()
        .orderBy('credits', 'desc')
        .limit(10)
        .select('id', 'name', 'credits')
        .get()
  ]);
  
  return {
    totalUsers,
    activeUsers,
    newUsersToday,
    topUsers
  };
}

Filtering with Multiple Options

async function getFilteredPosts(filters) {
  return await Post.query()
    .when(filters.category, (query, category) => {
      query.where('category_id', category);
    })
    .when(filters.author, (query, author) => {
      query.where('user_id', author);
    })
    .when(filters.published !== undefined, (query) => {
      query.where('is_published', filters.published);
    })
    .when(filters.dateFrom, (query, dateFrom) => {
      query.where('created_at', '>=', dateFrom);
    })
    .when(filters.dateTo, (query, dateTo) => {
      query.where('created_at', '<=', dateTo);
    })
    .when(filters.search, (query, search) => {
      query.where((q) => {
        q.where('title', 'like', `%${search}%`)
         .orWhere('content', 'like', `%${search}%`);
      });
    })
    .orderBy('created_at', 'desc')
    .paginate(filters.page || 1, filters.perPage || 15);
}

Next Steps

Learn Relationships

Connect different models together

Database Migrations

Set up and modify your database structure