Building Complex Queries in Craft CMS

A plugin for Craft CMS, demonstrating how to build complex or optimized queries by modifying an ElementCriteriaModel using buildElementsQuery().

For background on what this demo plugin is all about, see the post Craft CMS: Building Complex Queries by Extending the ElementCriteriaModel on the Tighten.co blog.

This plugin can be used as a starting point for adding your own advanced query logic, allowing you to perform queries that aren't possible using Craft's built-in methods. Using this plugin as a basis, you can, for instance:

• Add a join clause to a query, to search data from a third-party plugin
• Perform a complex join involving data from several tables
• Optimize a query in order to reduce the number of database queries performed
• Group, order, and aggregate results at the database level, rather than relying on the group filter in your Twig template

Installation

Add the buildquery folder to your craft/app/plugins directory, then activate the BuildQuery plugin in the Settings section of Craft's control panel.

Use

To begin your query, call the buildQuery variable from within a Twig template, and pass it an initial ElementCriteriaModel as source:

craft.buildQuery.source(...)

From there, you can chain additional query methods that you store in BuildQueryService, and finally grab your results with find:

craft.buildQuery.source(serviceEntries).countRelated(workEntries).find

Take a look at yourOwnMethod() in services/BuildQueryService.php for a good place to start building your own complex query logic.

Example

Using Craft's HappyLager demo site as an example, suppose we want to show the number of Work entries that are related to each Service entry in the Services navigation bar:

The typical way to do this would be to add a relatedTo query inside the loop where we output each Service, and grab each total:

{% for serviceEntry in craft.entries.section('services') %}

    {# Perform a `relatedTo` query for each element in `serviceEntry` #}
    {% set workCount = craft.entries.section('work').relatedTo(serviceEntry).total() %}

    <li>
        <a href="https://github.com/tighten/craft-build-query/blob/master/{{ serviceEntry.url }}" class="subfont">
            {{ serviceEntry.title }} · {{ workCount }}
        </a>
    </li>

{% endfor %}

The downside to this standard approach is that we are firing an additional database query for each Service. If we have only 6 services, this isn't a huge deal; but if we wanted to calculate totals for 50 elements, all those extra queries would start to add up fast.

Using buildElementsQuery(), we can optimize this count by attaching the relatedTo criteria to our original query, and adding a COUNT statement to our query's SELECT clause. This gives us the same results, but requires only 1 additional query—regardless of how many elements we have (n)—rather than performing n+1 queries.

{# Get ElementCriteriaModels for Service and Work sections #}
{% set serviceEntries = craft.entries.section('services') %}
{% set workEntries = craft.entries.section('work') %}

{% for serviceEntry in craft.buildQuery.source(serviceEntries).countRelated(workEntries).find %}

    <li>
        <a href="https://github.com/tighten/craft-build-query/blob/master/{{ serviceEntry.url }}" class="subfont">
            {{ serviceEntry.title }} · {{ serviceEntry.workCount }}
        </a>
    </li>

{% endfor %}

To see the plugin example in action:

1. Install Craft's HappyLager demo site
2. Add and activate this plugin (see Installation above)
3. Rename the existing template file templates/services/_entry.html to _entry_original.html for safekeeping
4. Replace it with the example template file from this plugin, located at examples/happylager/services/_entry.html
5. Visit the How It's Made section, and click one of the Section tiles on the page (e.g. Design)

Debugging

The plugin includes a few methods that are helpful when building and debugging a complex query. These can be called from within a Twig template to dump details about your query.

• To display details about the ElementCriteriaModel that your query is built on: {% do craft.buildQuery.debugCriteria %}
• To dump the underlying SQL query that is built after the ElementCriteriaModel has been converted to a dbCommand object: {% do craft.buildQuery.debugSql %}
• To show the results of your query in array form, before they get populated into EntryModels: {% do craft.buildQuery.debugResults %}