Element Queries

You can fetch elements (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 will automatically load the corresponding element when its URL is requested, and pass 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 global set by its handle) or relaxed (like a list of recently-updated entries).

Working with element queries consists of three steps:

  1. 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().
  2. Set some parameters. By default, element queries will be configured to return all elements of the specified type. You can narrow that down to just the elements you care about by setting parameters on the query.
  3. Execute the query. Use a query execution method to run the query and return results.

Relational fields also return element queries, which you can treat the same as step #1, above.

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

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

{# Execute the query and get the results #}
{% set entries = entryQuery.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.

#Element Types

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

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() %}
Matrix Block Queries
{% set matrixBlockQuery = craft.matrixBlocks() %}
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 imagesQuery = craft.assets() %}

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

{% set images = imagesQuery.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.

#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.

#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.

{% 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.

#collect()

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

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

{% 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.

#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.

{% 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.

{% 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().

{% 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.

{% 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:

{% 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.

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!

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





 


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

{% set results = craft.entries
  .section('news')
  .search(q)
  .all() %}
Searching
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 their categories) can lead to an “N+1 (opens new window)” problem, wherein each result from the main query triggers an additional query.

Eager loading is a means of pre-fetching those related elements in bulk, often eliminating all but one additional query. Auditing your templates for these problems can be tricky, but you may be able to narrow down what you’re looking for with these common bottlenecks:

  • Nested {% for %} loops;
  • Accessing or outputting data from Matrix blocks owned by multiple elements;
  • Getting information about an entry’s author, within a loop;
  • Using multiple asset transforms;
  • Using relational fields (either with .all(), .one(), or |first) within a loop;

Not all of these situations will require (or benefit from) eager-loading—the goal is only to consider which of your projects’ features may be candidates.

#Caching Element Queries

Results can be cached with the cache() method:

{% 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:

{# 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?

{# 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()) do not.

In these cases, you may need to use craft\helpers\ElementHelper::fieldColumnFromField() (opens new window) in combination with craft\services\Fields::getFieldByHandle() (opens new window) to translate a field handle to a content table column name.

#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.

{# 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.

Custom field column names will be automatically resolved when using select(). 4.3.0+ In earlier versions, you may find that some field’s database columns include a random suffix and will require translating the field handle with craft\helpers\ElementHelper::fieldColumnFromField() (opens new window).

#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 Laravel Collection (opens new window).
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.