4

I know that to get the entries I can do:

craft.entries.all()

but instead of getting the whole collection of entries, I would like to get an array of titles from those entries (or any other field).

Sort of like craft.entries.all('myCustomField')

How is it possible to achieve it?

Thanks

1
  • 1
    Answered for completeness, as the ability to do this changed pretty significantly as of 3.7.0—but if you just need an array of titles to work with and aren't super concerned about the overhead of fetching + loading all the underlying Elements, you can use the column() filter: {% titles = entries | column('title') %}. This will also work with any field handle (or method) that is available on the collection you're working with! Oct 14, 2021 at 3:06

3 Answers 3

9

❗️ This solution will be dramatically simplified in Craft 4.3!


Unfortunately, the answer differs pretty dramatically for native properties with "stable" column names and for Content, which (as of Craft 3.7.0) now contain a "suffix" that (in most any other case) is not a concern for developers. More on that in a moment.

Titles

For titles, it's as simple as using the .select() method native to the underlying Query class:

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

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

Note the .column() query execution method—this basically means "just return the values in the first selected column"!

Alternatively, you can select multiple columns, like this:

{% set titles = craft.entries
    .section('mySection')
    .select(['title', 'slug'])
    .asArray()
    .all() %}

{# -> [{ 'title': 'Post A', 'slug': 'post-a' }, ...] #}

Note the .asArray() method, which ensures Element models aren't populated with the limited subset of data, after the rows come back!

Content

Please correct me if I'm wrong about this—my sense is that this feels too complicated, but it's based on how the core ElementQuery maps unpredictable column names! 🤷

This picture is much murkier, and currently only works if you either:

  1. …know the field columns' suffix beforehand (which you may, as Project Config and Templates are often versioned + deployed together—and the suffix is easily accessible in the field's yml file);
  2. …can load and call ElementHelper::fieldColumn(…) to generate a column name, on the fly.

Suppose you do know the suffix:

{% set minimalDataset = craft.entries
    .select([
        'title',
        'field_myCustomField_otfeztjq as myCustomField'
    ])
    .asArray()
    .all() %}

Not great, right? The only relief we get here is that the columns can be renamed on-the-fly, using the as keyword.

For the sake of completeness, here's an example in PHP that satisfies #2, above:

use Craft;
use craft\elements\Entry;
use craft\helpers\ElementHelper;

// Initialize a query, and ensure results are returned as a plain array:
$query = Entry::find()->asArray();

// Load up our field's definition:
$field = Craft::$app->getFields()->getFieldByHandle('myFieldHandle');

// Be aware that the Date field (as well as some plugin-provided fields) may actually return *multiple* column names, and would need to be merged, rather than declared as part of this Select statement!
$fieldColumn = ElementHelper::fieldColumnFromField($field);

$query->select([
    'title',
    "{$fieldColumn} as fieldAlias",
]);

$query->all();

See @MoritzLost’s answer, below, for a more complete and reusable version of this approach!

2
  • 1
    Hardcoding the suffix is indeed not great, though it appears to be the simplest way to use custom queries right now. But you can also write a small twig extension to expose the field column helper method to Twig, see my answer below.
    – MoritzLost
    Oct 14, 2021 at 7:56
  • Fantastic answer - I learnt some cool stuff there! Thanks! Mar 10, 2022 at 13:03
7

Update for Craft 3.7

Craft 3.7 adds a new field property columnSuffix which is way easier to use than my example code below.

Now you can simply access any field's column suffix in Twig like this:

{% set columnSuffix = craft.app.fields.getFieldByHandle('my_custom_field').columnSuffix %}

See the old answer below if you're still on Craft < 3.7.


Old answer for Craft < 3.7

Just to expand on the caveat regarding field suffixes in Craft 3.7+, I asked on Github what the recommended way to include field suffixes in a custom query is. Here's brandonkelly's response:

The easiest way to do it would be to just hardcode the column name in your template. It won’t change between environments or anything.

That said, it does feel kind of wrong to hardcode a field suffix like this. Unfortunately, the ElementHelper::fieldColumnFromField method is not exposed to Twig, so there's no straightforward way to use it. The closest I've come is this code:

{% set body_field = craft.app.fields.getFieldByHandle('body') %}
{% set ElementHelper = create('craft\\helpers\\ElementHelper') %}
{% set body_column = ElementHelper.fieldColumnFromField(body_field) %}

{% set entries = craft.entries().select(['title', body_column]).all() %}

However, that's still pretty verbose and might not work in the future as per brandonkelly's response:

@MoritzLost I wouldn’t instantiate an ElementHelper instance in your template; the class should really be marked as absract as it’s not meant to be instantiated.

I guess the most future-proof way would be to write a Twig extension that adds a utility method to get the field suffix from the field name:

<?php

namespace modules\Twig\Extension;

use Craft;
use craft\helpers\ElementHelper;
use Twig\Extension\AbstractExtension;
use Twig\TwigFunction;

class QueryHelperExtension extends AbstractExtension
{
    public function getFunctions()
    {
        return [
            new TwigFunction('fieldColumnFromFieldHandle', [self::class, 'fieldColumnFromFieldHandle']),
        ];
    }

    /**
     * Get the database column name for a field to use in custom database queries.
     *
     * @param string $fieldHandle
     * @return string
     */
    public static function fieldColumnFromFieldHandle(string $fieldHandle): string
    {
        $field = Craft::$app->fields->getFieldByHandle($fieldHandle);
        return ElementHelper::fieldColumnFromField($field);
    }
}

Make sure to register the extension:

<?php

namespace modules\Twig;

use Craft;
use modules\Twig\Extension\QueryHelperExtension;

class TwigExtensionModule extends \yii\base\Module
{
    public function init()
    {
        Craft::$app->view->registerTwigExtension(new QueryHelperExtension);

        parent::init();
    }
}

Now you can use that in Twig to get field column names more easily:

{% set bodyColumn = fieldColumnFromFieldHandle('body') %}
6
  • 1
    That is very cool. Thanks! Mar 10, 2022 at 13:05
  • 1
    Thank you for this. It helped me. I had some problems with getting a super table columns' suffix, though. I had to modify the fieldColumnFromFieldHandle method a bit like so $result = Craft::$app->getDb()->createCommand("SELECT columnSuffix FROM fields WHERE handle = :handle;", ['handle' => $fieldHandle])->queryOne(); Feb 20, 2023 at 11:16
  • @ErikPöhler Whate kind of problems? Going through ElementHelper should be more robust and shouldn't require an additional database query. Did the module code in my answer throw an error somewhere?
    – MoritzLost
    Feb 21, 2023 at 9:56
  • @ErikPöhler By the way, this became way easier in Craft 3.7, I've updated my answer accordingly. If you're working with Craft 3.7 or above, I recommend this simpler approach. Thanks for the nudge!
    – MoritzLost
    Feb 21, 2023 at 10:04
  • 1
    The problem was that $field was null, but needs to be a FieldInterface. Your code worked for fields with global context, though. (Supposedly one needs to pass some superTable context). Having said that, the new solution looks way better. I appreciate, you shared it. Feb 22, 2023 at 17:53
0

Ended up using GraphQL

Let's say we have an entry, and from that entry we need to retrieve the "title", and the custom field "relatedProducts", which is an entry type field, and from those related entries we need to retrieve the "title", "url" and an image URL from a custom field called "productImage"

That sounds a bit complicated to just do it with twig queries.

So instead of those, I ran the following query:

   {% set relatedProducts = gql('{
          entry (id: "' ~ entry.id ~ '") {
              ... on productsCategories_productBasic_Entry {
                  title
                  relatedProducts {
                       title
                       url
                       productImage {
                          url
                       }
                  }
               }
         }
}') %}

After I dump the values ({% dd(relatedProducts) %}), I get:

[
    'data' => [
        'entry' => [
            'title' => 'track - grey'
            'relatedProducts' => [
                0 => [
                    'title' => 'Track - black'
                    'url' => 'https://mysite.ddev.site/tracks-and-accessories/track-black'
                    'productImage' => [
                        0 => [
                            'url' => 'https://mysite.ddev.site/uploads/280x180.png'
                        ]
                    ]
                ]
                1 => [
                    'title' => 'Track - white'
                    'url' => 'https://mysite.ddev.site/tracks-and-accessories/track-white'
                    'productImage' => []
                ]
            ]
        ]
    ]
]

which is what I needed :)

3
  • Could you elaborate on where this data is being used? In Twig to render HTML, or JSON via AJAX? Hoping to understand the underlying intent to make sure we aren't missing some simpler solution for you! Sep 29, 2022 at 22:27
  • Thanks @AugustMiller! I need to pass it to a Vue component, which I'm currently sending as JSON Oct 2, 2022 at 21:33
  • 1
    Ok, fair enough! This kind of data massage will always be challenging with Twig; GraphQL sounds like a great option. Pulling the data directly from Vue may be even easier, through the native GraphQL API—or by setting up an Element API endpoint to do the lookup and transformation in PHP-land. ✌️ Oct 3, 2022 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.