How to query an entry by a custom field in Craft 3

Question

How do you query an Element by a custom field in Craft 3? My first try, according to craftcms/docs was:

$country = Entry::find()
                ->section('countries')
                ->where(['LIKE', 'code', 'CA'])->one()

But this throws an exception. Here code is a custom text field. I've also tried some answers on stack exchange and many other combinations but they're for Craft 2 and neither they work.

Answer 1

Your where clause is actually a query based where so you have to select the database fields instead of the field handles. When you want to do it with where you have to make it like that

$country  = Entry::find()->section('countries')->where([
    'content.field_code'    => ['like', 'CA']
])->all();

This is the correct documentation right now.

Edit: you might want to call the getRawSql() function in order to see the query and all selected rows. When you use your code you'll see why your sql throws an exception

Edit2:
You don't need to read the following part, these are just some explanations to the content.field_codepart - if you are interested in Craft go ahead if not skip it.

Not sure if you know that but Craft adds the prefix field_ to every fieldhandle in the content table (the table where Craft stores your entire content). So for every field you create the database includes a column field_+$field->handle that's why you have to include the field_ in before your handle in the query above.

However if you are going to change that prefix for any reasons you could use Craft::$app->getContent()->fieldColumnPrefix in order to fetch the current column prefix. I doubt many people will do this but I wanted to make sure you know it.

And the ElementQuery (the class you actually call when you do Entry::find() contains the following code

/**
 * @var string|null The content table that will be joined by this query.
 */
public $contentTable = '{{%content}}';

This is hard coded so only the developers from craft can make really deep changes here (unless you overwrite the structure in Craft)

So in the end: craft left joins the content table and selects all columns. That's why you have to use content.field_+$fieldHandle

Answer 2

I might be missing some important SQL-ism, here, but I think the question has a much simpler answer. The ElementQuery docs bury it a bit, and I wonder if many new Craft developers have trouble understanding the power of it, as a result.

From the documentation:

Tip
Most custom fields support element query parameters as well, named after the field handles.

This means that, when you are looking to filter based on some custom field content, Craft lets you set up the constraints just like you would the built-in Element attributes:

$userQuery = User::find();

// If you have a field with the handle `myCustomAgeFieldHandle`
// attached to your User element, you can use it as a filter:
$userQuery->myCustomAgeFieldHandle = ['and', '>= 80', '< 90'];

// Alternatively, you can use the chained syntax:
// $userQuery->myCustomAgeFieldHandle(['and', '>= 80', '< 90']);

$octogenarians = $userQuery->all();

Similarly, you can use the same logic for other Element types (Entries, Assets, MatrixBlocks, etc.), specifying values for any of the custom fields you use.

Note: Some fields don't have great support for this, i.e. Multi-checkbox and Table, as they store data as JSON in their columns, so the recommendation here is usually to use Categories or MatrixBlocks to store the data so that they're queryable in the same manner.

Behind the scenes, Craft makes this possible via two Behavior classes that are dynamically generated based on your Element (Entry/Asset/User) configurations (see the Element's ContentBehavior and the corresponding ElementQueryBehavior..