Element Queries

You can fetch elements (like entries, categories, assets, etc.) in your templates or PHP code using element queries.

Suppose you’ve already created a section for news posts and configured a URL scheme. Craft automatically loads the corresponding entry element when its URL is requested, and exposes it to the template under an entry variable. This is convenient—but it’s rare that a page only refers to a single piece of content! What if we want to show a list of other recent posts, in a sidebar? Element queries are Craft’s way of loading elements anywhere you need them.

Element queries can be hyper-specific (like loading a single global set by its handle) or relaxed (like a list of recently-updated entries).

Working with element queries consists of three steps:

Create the element query. Calling the “factory function” corresponding to the element type you want to fetch. For entries, this is craft.entries(); for categories, craft.categories().
Set some parameters. By default, element queries will be configured to return all elements of a given type. You can narrow that down to just the elements you care about by setting parameters on the query.
Execute the query. Use a query execution method to run the query and return results.

Relational fields also return element queries (except when eager-loaded), which you can treat the same as the result of step #1, above.

Here’s what this process looks like, in practice:

Twig
PHP

{# Create an entry query and set some parameters on it #}
{% set postsQuery = craft.entries()
  .section('news')
  .orderBy('postDate DESC')
  .limit(10) %}

{# Execute the query and get the results #}
{% set posts = postsQuery.all() %}

You can eliminate the intermediate variable and flatten this into a single statement by chaining the execution method on the end:

Twig
PHP

{% set posts = craft.entries()
  .section('news')
  .orderBy('postDate DESC')
  .limit(10)
  .all() %}

# Types + Parameters

Each type of element has its own function for creating element queries, and they each have their own parameters you can set. All element types expose parameters for their custom fields.

# Element Types

See the query reference section of each element type for more details on working with them:

Address Queries →

{% set addressQuery = craft.addresses() %}

Asset Queries →

{% set assetQuery = craft.assets() %}

Category Queries →

{% set categoryQuery = craft.categories() %}

Entry Queries →

{% set entryQuery = craft.entries() %}

Global Set Queries →

{% set globalQuery = craft.globals() %}

Tag Queries →

{% set tagQuery = craft.tags() %}

User Queries →

{% set userQuery = craft.users() %}

# Parameters

Parameters are set using methods after creating an element query, or by passing in key-value pairs to the factory function:

{% set images = craft.assets()
  .kind('image')
  .all() %}

{# ...or... #}

{% set images = craft.assets({
  kind: 'image',
}).all() %}

{# ...or if you’re fancy, set some parameters conditionally: #}

{% set assetsQuery = craft.assets() %}

{% if craft.app.request.getParam('onlyImages') %}
  {# Effectively the same as chaining query methods: #}
  {% do assetsQuery.kind('image') %}
{% endif %}

{% set images = assetsQuery.all() %}

Query methods (except for those that execute a query) modify some internal properties and return the query itself, allowing you to chained more methods together—just like Craft’s fluent config syntax!

All element queries support a standard set of methods (like .id(), .title(), and .search()). These are documented alongside the element type-specific parameters (like .kind() in the example above).

Typically, parameters make a query more specific—but setting a single parameter more than once will replace the previous constraint. Similarly, setting a parameter to null can broaden a query by removing an existing constraint.

# Querying with Custom Fields

In addition to native query parameters, Craft automatically injects methods for each of your custom fields.

For example, if we wanted to find entries in a Cars section with a specific paint color stored in a dropdown field, we could perform this query:

{% set silverCars = craft.entries()
  .section('cars')
  .paintColor('silver')
  .all() %}

Custom field parameters can be combined for advanced filtering—in this example, we’re also applying a pair of constraints to a date field:

{% set silverCars = craft.entries()
  .section('cars')
  .paintColor(['silver', 'gold'])
  .modelYear('>= 1990', '<= 2000')
  .all() %}

See each field type’s documentation for what kinds of values you can use.

# Case-Sensitivity

Queries against textual fields (Color, Country, Email, Icon, Link, Plain Text, and Table) are case-sensitive, by default.

To make a query case-insensitive, pass an object with the special caseInsensitive key:

{% set cars = craft.entries()
  .section('cars')
  .brand({
    value: 'bmw',
    caseInsensitive: true,
  })
  .all() %}

Dropdown, Radio, Checkboxes, and other fields whose only allowable values are determined by the field’s configuration are always handled in a case-sensitive way.

# Reusing Queries

Sometimes, you might want to run a number of similar queries. Take this case, where we want to show some information about upcoming events at a library:

{# Assuming we're on a page for a specific branch: #}
{% set upcomingEvents = craft.entries()
  .section('events')
  .dateStart(">= #{now|atom}")
  .branch(entry) %}

{% set weekEvents = clone(upcomingEvents)
  .dateEnd("< #{now|modify('+1 week')|atom}") %}
Events in the next week: {{ weekEvents.count() }}

{% set monthEvents = clone(upcomingEvents)
  .dateEnd("< #{now|modify('+1 month')|atom}") %}
Events in the next month: {{ monthEvents.count() }}

The clone() function is used to copy a base query, before setting additional parameters. Parameters set on the base query (like dateStart(), above) will affect all subsequent queries, unless explicitly unset.

Note that we are only executing the weekEvents and monthEvents queries! Cloning an executed query will just copy the results.

# Relational Fields

Any time you access a relational field (specifically, one that hasn’t been eager-loaded), Craft automatically clones it:

{% set relatedDocuments = entry.attachments.type('document').all() %}
{% set relatedArticles = entry.attachments.type('article').all() %}
{% set recentAttachments = entry.attachments
  .dateCreated(">= #{now|date_modify('-1 week')}")
  .orderBy('dateCreated DESC')
  .limit(5)
  .all() %}

In this example, each time entry.attachments is accessed, it returns a fresh element query prepared to fetch the explicitly related elements, in the order they were defined in the control panel. Calling .type('...') on the first two queries will not affect the third query. If instead you captured the query in an intermediate variable, each param would be applied in sequence on a single query:

{% set attachmentsQuery = entry.attachments %}
{% set relatedDocuments = attachmentsQuery.type('document').all() %}
{% set relatedArticles = attachmentsQuery.type('article').all() %}
{% set recentAttachments = attachmentsQuery
  .dateCreated(">= #{now|date_modify('-1 week')}")
  .orderBy('dateCreated DESC')
  .limit(5)
  .all() %}

The first two sets of results here will contain the expected elements—relatedDocuments modifies the base query, and then relatedArticles overwrites the type param—but the third will retain the last-set type value and apply additional dateCreated and orderBy params. Instead of containing the latest five attachments of any type, it will only include the latest five articles.

# Executing Element Queries

Once you’ve defined your parameters on the query, there are multiple functions available to execute it, depending on what you need back.

Craft also makes it easy to display the results of an element query across multiple pages with pagination.

# `all()`

The most common way to fetch a list of results is with the all() method, which executes the query and returns an array of populated element models. The resulting elements will always be of the type that the query started with—assets come back from asset queries, categories from category queries, and so on.

Twig
PHP

{% set entries = craft.entries()
  .section('news')
  .limit(10)
  .all() %}

Declaring a limit() and executing a query with all() may seem like a contradiction, but this is a totally valid query! all() doesn’t override the limit(); rather, it returns all results that meet the current criteria—one of which just happens to be a cap on the number of allowed results. Regardless of the number of results, .all() always returns an array—even if it’s empty.

# `collect()`

Calling .collect() to execute a query will perform the same database call as .all(), but the results are wrapped in a Collection.

Collections can simplify some common array manipulation and filtering tasks that are otherwise awkward in the template or query builder:

Twig
PHP

{% set entries = craft.entries()
  .section('news')
  .with(['category'])
  .limit(10)
  .collect() %}

{% set categoriesDescription = entries
  .pluck('category')
  .collapse()
  .pluck('title')
  .join(', ', ' and ') %}
Posted in: {{ categoriesDescription }}

You can also call .all() and wrap the results in a collection yourself, with the collect() Twig function.

Review some of the optimization tips to see if built-in query methods can accomplish what you need.

For example, loading and populating hundreds of element models into a collection solely to calculate an average field value may not be necessary; the database is highly optimized for tasks like this!

# `one()`

If you only need a single element, call one() instead of all(). It will either a populated element model or null if no matching element exists.

Twig
PHP

{% set entry = craft.entries()
  .section('news')
  .slug('hello-world')
  .one() %}

# `exists()`

If you just need to check if any elements exist that match the element query, you can call exists(), which will return either true or false.

Twig
PHP

{% set exists = craft.entries()
  .section('news')
  .slug('hello-world')
  .exists() %}

# `count()`

If you want to know how many elements match an element query, call count().

Twig
PHP

{% set count = craft.entries()
  .section('news')
  .count() %}

The limit and offset parameters will be ignored when you call count().

# `ids()`

If you just want a list of matching element IDs, you can call ids(). This returns an array of integers.

Twig
PHP

{% set entryIds = craft.entries()
  .section('news')
  .ids() %}

# `column()`

Combined with a single-column selection, the column() execution method will return a scalar value for each row instead of an object:

Twig
PHP

{% set entries = craft.entries()
  .section('news')
  .select(['title'])
  .column() %}

{# -> ['Post A', 'Post B', 'Post C'] #}

An array of scalar values is returned, the type of which depends on the column. Elements are not populated when using column(), so methods and properties you may be accustomed to using after other queries will not be available.

# Pagination

Craft provides the {% paginate %} tag to simplify the process of splitting results into pages with a stable URL scheme based on the pageTrigger setting.

The paginate tag accepts an element query, sets its offset param based on the current page, and executes it. The number of results per page is determined by the query’s limit param, or defaults to 100.

{# Prepare your query, but don’t execute it: #}
{% set newsQuery = craft.entries()
  .section('news')
  .orderBy('postDate DESC') %}

{# Paginate the query into a `posts` variable: #}
{% paginate newsQuery as pageInfo, posts %}

{# Use the `posts` variable just like you would any other result set: #}
{% for post in posts %}
  <article>
    <h2>{{ post.title }}</h2>
    {# ... #}
  </article>
{% endfor %}

Paginating a query will only work if the results come back in a stable order and the page size is kept consistent. Using randomized values in query params or in an orderBy clause will be disorienting for users.

Results from a search query are perfectly fine to paginate.

# Navigating Pages

In our example, the pageInfo variable (a Paginate (opens new window) instance) has a number of properties and methods to help you work with paginated results. The variable can be named anything you like, so long as references to it are updated.

first: Number of the first element on the current page. For example, on the second page of 10 results, first would be 11.
last: Number of the last element on the current page. For example, on the first page of 10 results, last would be 10.
total: Total number of results, across all pages.
currentPage: The current page. Equivalent to craft.app.request.getPageNum().
totalPages: The total number of pages the results are spread across. The last page of results may not be complete.
getPageUrl(page): Builds a URL for the specified page of results.
getFirstUrl(): Builds a URL for the first page of results. Equivalent to pageInfo.getPageUrl(1).
getLastUrl(): Builds a URL for the last page of results. Equivalent to pageInfo.getPageUrl(pageInfo.totalPages).
getNextUrl(): Get a URL for the next page of results. Returns null on the last page of results.
getPrevUrl(): Get a URL for the previous page of results. Returns null on the first page of results.
getNextUrls(num): Gets up to num next page URLs, indexed by their page numbers.
getPrevUrls(num): Gets up to num previous page URLs, indexed by their page numbers.
getRangeUrls(start, end): Returns a list of URLs indexed by their page number. The list will only include valid pages, ignoring out-of-range start and end values.
getDynamicRangeUrls(max): Returns up to max page URLs around the current page, indexed by their page numbers.

The values above use a one-based index, so they are human-readable without any additional work.

# Examples

You can display a summary of the current page using pageInfo.total, pageInfo.first, and pageInfo.last:

Showing {{ pageInfo.first }}–{{ pageInfo.last }} of {{ pageInfo.total }} results.

Next and previous links are simple:

<nav role="navigation" aria-label="Search result pagination">
  {% if pageInfo.getPrevUrl() %}
    <a href="{{ pageInfo.getPrevUrl() }}">Previous Page</a>
  {% endif %}
  {% if pageInfo.getNextUrl() %}
    <a href="{{ pageInfo.getNextUrl() }}">Next Page</a>
  {% endif %}
</nav>

We could improve this for screen readers by including specific page numbers in the labels:

{% set prevLinkSummary = "#{pageInfo.currentPage - 1} of #{pageInfo.totalPages}" %}

{{ tag('a', {
  text: 'Previous Page',
  href: pageInfo.getPrevUrl(),
  aria: {
    label: "Previous page (#{prevLinkSummary})"
  }
}) }}

We’re using the tag() Twig function to make this a little more readable, but its output is equivalent to a normal anchor element.

More advanced pagination links are also possible with getDynamicRangeUrls():

<nav role="navigation" aria-label="Search result pagination">
  <ul>
    {% for p, url in pageInfo.getDynamicRangeUrls(5) %}
      <li>
        {{ tag('a', {
          text: p,
          href: url,
          aria: {
            label: "Go to page #{p} of #{pageInfo.totalPages}",
          },
        }) }}
      </li>
    {% endfor %}
  </ul>
</nav>

Notice how our loop uses the keys (p) and values (url) from the returned array—Craft assigns each URL to a key matching its page number!

# Search

Craft gives you access to its search index from any element query. Use the search param to narrow results by keywords:

Twig
PHP

{% set q = craft.app.request.getQueryParam('search') %}

{% set results = craft.entries
  .section('news')
  .search(q)
  .all() %}

Learn about the supported syntaxes for plain-text search.

# Performance and Optimization

When you start working with lots of data, it’s important to consider how queries affect your pages’ load time. While the {% cache %} tag can be used strategically to avoid major slowdowns, it’s only one of many tools at your disposal.

Turn on the Debug Toolbar in your user’s preferences to profile your memory usage and database query counts.

# Eager Loading

Displaying a list of elements and one or more elements related to each of them (say, blog posts and any tags or categories attached to them) can lead to an “N+1” problem, wherein each result from the main query triggers an additional query. Craft addresses this with eager-loading.

Eager-Loading Elements

Eager-loading is a means of fetching nested or related elements in bulk.

# Caching Element Queries

Results can be cached with the cache() method:

Twig
PHP

{% set entries = craft.entries()
  .section('news')
  .limit(10)
  .cache()
  .all() %}

This cache is separate from fragments cached via {% cache %} template tags, and will only match subsequent queries that have all the same parameters. Caching a query does not guarantee better performance, but it can be used strategically—say, to memoize a scalar query result inside a loop (like the total number of entries in a list of categories).

The cache() method accepts a duration argument, and defaults to your cacheDuration.

Craft registers an ElementQueryTagDependency (opens new window) for you by default, so cache dependencies and invalidation are handled automatically.

# Large Result Sets

Sometimes, a query will simply depend on a large number of elements (and pagination is not possible), or it needs to use the most current data available (so caching is off the table).

Populating element models can be resource-intensive, and loading many thousands of records can exhaust PHP’s memory limit. Let’s look at some common places where queries can be optimized to avoid this bottleneck.

# Counting

In this example, we just need the number of active users:

Slow
Optimized

{# Loads and populates all users, then gets the length of the array: #}
{% set totalUsers = craft.users().status('active').all()|length %}

In addition to the memory footprint of the optimized query being many orders of magnitude smaller, we’re also avoiding a huge amount of data transfer between the PHP process and database server!

Using the length filter on a query (before it’s been run) will automatically call its count() execution method to prevent inadvertent performance issues. Other situations in which queries are treated as arrays may not be optimized in the same way.

# Arithmetic Operations

Counting isn’t the only operation that the database can do for you! What if we wanted to find the minimum and maximum values for a given field?

Slow
Optimized

{# Loads field data for every race, then throws out all but one property: #}
{% set races = craft.entries()
  .section('races')
  .all() %}
{% set fastestTime = min(races|column('winningTime')) %}
{% set slowestTime = max(races|column('winningTime')) %}

scalar() is just an execution method that returns the first column from the first result—it will always produce a simple, “scalar (opens new window)” value.

While select() and orderBy() accept field handles and ambiguous columns, some SQL functions and expressions (like MIN() or SUM()) may not.

# Lean Selections

Combining a narrower selection with an execution method that returns results as an array (or explicitly calling toArray() while preparing a query) can significantly reduce the amount of memory a query requires.

Slow
Optimized

{# Load all donor entries with complete native + custom field data: #}
{% set donors = craft.entries()
  .section('donors')
  .all() %}

<ul>
  {% for donor in donors %}
    <li>{{ donor.title }} — {{ donor.lifetimeGiftAmount|money }}</li>
  {% endfor %}
</ul>

The pairs() execution method is a shorthand for creating a key-value hash from the first two columns of each row. Collisions can occur, so it’s safest to use a column you know will be unique for your first selection!

Not all attributes can be fetched this way—element URLs, for instance, are built on-the-fly from their URIs and site’s base URL. Relational data may also be more difficult to work with, as it often has to be eager-loaded alongside fully-populated element models.

# Advanced Element Queries

Element queries are specialized query builders (opens new window) under the hood, so they support most of the same methods provided by craft\db\Query (opens new window). The most common methods appear below—argument lists are non-exhaustive, and are only provided to differentiate them.

You may call craft\db\Query::asArray() (opens new window) to skip populating element models with results and return matching rows’ data as an associative array. Altering selections in particular can make elements behave erratically, as they may be missing critical pieces of information.

# Selections

Selections modify what columns and rows are returned.

select() (opens new window): Define a list of columns to SELECT.
addSelect() (opens new window): Add columns to the existing selection.
distinct() (opens new window): Return only rows that have a unique combination of values in the provided column(s).
groupBy($columns) (opens new window): Combine or flatten database rows based on the value of one or more columns. Often used in combination with aggregate selections like SUM(columnName).
limit($n) (opens new window): Set a maximum number of results that can be returned.
offset($n) (opens new window): Skip the specified number of matching rows.

# Joins

In most cases, Craft automatically JOINs the appropriate tables so that your elements are populated with the correct data. However, additional JOINs can be useful in plugin development or for doing deeper analysis of your content.

innerJoin($table, $condition) (opens new window): Adds an INNER JOIN clause for the target table, using the provided condition.
leftJoin($table, $condition) (opens new window): Adds a LEFT JOIN clause for the target table, using the provided condition.
rightJoin($table, $condition) (opens new window): Adds a RIGHT JOIN clause for the target table, using the provided condition.

JOIN conditions are generally expected to be in this format:

$popularAuthors = craft\elements\Entry::find()
  ->select([
    'users.fullName as name',
    'COUNT(*) as postCount',
  ])
  ->groupBy('entries.authorId')
  ->leftJoin('{{%users}}', '[[entries.authorId]] = [[users.id]]')
  ->asArray()
  ->all();

Craft prepares two queries when fetching elements (a main query and a “subquery”) and applies JOINs to both, so that you can use the tables for filtering and for selections. Read more about the architecture of element queries in the extension documentation.

Adding columns to your selection from other tables may cause errors when populating elements, as they will not have a corresponding class property. Call asArray() to return your results as a plain associative array, or consider attaching a Behavior.

# Conditions

Exercise caution when using these methods directly—some will completely overwrite the existing query conditions and cause unpredictable results like allowing drafts, revisions, or elements from other sites to leak into the result set.

Specific element type queries and custom field methods often provide a more approachable and reliable API for working with the database, and will modify the query in non-destructive ways.

where() (opens new window): Directly set the query’s WHERE clause. See the warning, above.
andWhere() (opens new window): Add expressions to the WHERE clause. Useful if the provided element type-specific query methods can’t achieve an advanced condition required by your site or application.
orWhere() (opens new window): Starts a new WHERE clause.
filterWhere() (opens new window): Same as where(), but ignores null values in the passed conditions.
andFilterWhere() (opens new window): Same as andWhere(), but ignores null values in the passed conditions.
orFilterWhere() (opens new window): Same as orWhere(), but ignores null values in the passed conditions.

# Query Execution

Some of these methods are discussed in the Executing Element Queries section.

all() (opens new window) — craft\base\Element[]: Array of populated element models.
collect() (opens new window) — Illuminate\Support\Collection: Same as all(), but wrapped in a Collection.
one() (opens new window) — craft\base\Element|null: Element model or null if none match the criteria.
nth($n) (opens new window) — craft\base\Element|null: Element model or null if one doesn’t exist at the specified offset.
exists() (opens new window) — boolean: Whether or not there are matching results.
count() (opens new window) — int: The number of results that would be returned.
column() (opens new window) — array: Array of scalar values from the first selected column.
scalar() (opens new window) — int|float|string|boolean: A single, scalar value, from the first selected column of the matching row.
sum($column) (opens new window) — int|float: Total of values in $column across all matching results
average($column) (opens new window) — int|float: Average of all $column values matching results
min($column) (opens new window) — int|float: Minimum value in $column among matching results
max($column) (opens new window) — int|float: Maximum value in $column among matching results
pairs() (opens new window) — array: The first selected column becomes the returned array’s keys, and the second, its values. Duplicate keys are overwritten, so the array may not have the same number of elements as matched the query.

When customizing an element query, you can call getRawSql() (opens new window) to get the full SQL that is going to be executed by the query, so you have a better idea of what to modify.

{{ dump(query.getRawSql()) }}

# Headless Applications

Craft can act as a headless content back-end for your static or client-rendered website. There are two main ways of making content available to applications that exist outside Craft’s built-in Twig templating layer:

# Element API

The first-party Element API (opens new window) allows you to map endpoints to element queries with a combination of static and dynamic criteria and serve JSON-serialized results.

# GraphQL

Craft includes a GraphQL API with configurable schemas. Many of the same element query basics apply when accessing elements via GraphQL.

For security reasons, not all query builder features are available via GraphQL. Some advanced queries may need to be executed separately and combined by the client.

Element Queries

# Types + Parameters

# Element Types

# Parameters

# Querying with Custom Fields

# Case-Sensitivity

# Reusing Queries

# Relational Fields

# Executing Element Queries

# all()

# collect()

# one()

# exists()

# count()

# ids()

# column()

# Pagination

# Navigating Pages

# Examples

# Search

# Performance and Optimization

# Eager Loading

# Caching Element Queries

# Large Result Sets

# Counting

# Arithmetic Operations

# Lean Selections

# Advanced Element Queries

# Selections

# Joins

# Conditions

# Query Execution

# Headless Applications

# Element API

# GraphQL

Was this page helpful?

# `all()`

# `collect()`

# `one()`

# `exists()`

# `count()`

# `ids()`

# `column()`