5.x Filtering against a field that's shared across many entry types is cumbersome

[johnrich85]

If you want to fetch multiple types in a single query & filter against a field that's shared across them, it's more cumbersome in 5.x than in 4.x. Mostly because there's no longer a single column name to target (instead theres a uid per type).

The code below demonstrates the issue:

In 4.x:

        $this->subQuery->andWhere(
          ['not', ['content.field_myField' => 'abc']]
        );

in 5.x:

        $sections = [];

        $types = collect(Craft::$app->getEntries()->getAllSections())
            ->filter(fn(Section $section) => empty($sections) || in_array($section->id, $sections))
            ->flatMap(fn(Section $section) => $section->entryTypes);

        $types->each(function (EntryType $type) {
            $fieldSql  = $type->getFieldLayout()
                ->getFieldByHandle('myField')
                ->getValueSql();

            $this->subQuery->andWhere([
                'not',
                ['like', $fieldSql, '"abc"']
            ]);
        });

I understand the reasons for that, and presumably there's not much to be done about it so I'm mostly wondering if this can be abstracted/if we can get a method/service or something to assist with it.

[brandonkelly]

Most field types provide their own element query params that simplify filtering results by them.

For example, if this is a Plain Text field, you could do:

$elementQuery->myFieldHandle('not abc');
// or:
$elementQuery->myFieldHandle(['not', 'abc']);

And as of Craft 5.6, you can now refer to custom field handles within conditions passed to where() (et al.), and they will automatically get replaced with references to the individual sub-JSON values.

$elementQuery->andWhere(['not', ['myFieldHandle' => 'abc']]);

(Same goes for orderBy and select methods - #16157.)