<p>I might be missing some important SQL-ism, here, but I think the question has a much simpler answer. The <a href="https://docs.craftcms.com/v3/dev/element-queries/" rel="nofollow noreferrer"><code>ElementQuery</code> docs</a> bury it a bit, and I wonder if many new Craft developers have trouble understanding the power of it, as a result.</p>

<p>From the documentation:</p>

<blockquote>
  <p><strong>Tip</strong><br>
  Most custom fields support element query parameters as well, named after the field handles.</p>
</blockquote>

<p>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:</p>

<pre class="lang-php prettyprint-override"><code>$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-&gt;myCustomAgeFieldHandle = ['and', '&gt;= 80', '&lt; 90'];

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

$octogenarians = $userQuery-&gt;all();
</code></pre>

<p>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.</p>

<blockquote>
  <p><strong>Note:</strong> 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.</p>
</blockquote>

<p>Behind the scenes, Craft makes this possible via two <code>Behavior</code> classes that are dynamically generated based on your Element (Entry/Asset/User) configurations (see the Element's <a href="https://github.com/craftcms/cms/blob/develop/src/behaviors/ContentBehavior.php.template" rel="nofollow noreferrer"><code>ContentBehavior</code></a> and the corresponding <a href="https://github.com/craftcms/cms/blob/develop/src/behaviors/ElementQueryBehavior.php.template" rel="nofollow noreferrer"><code>ElementQueryBehavior</code></a>..</p>

CC BY-SA 4.0
2019-12-20T19:38:13.923Z
2019-12-20T19:38:13.923Z
August Miller
<p>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</p>

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

<p><a href="http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html" rel="noreferrer">This</a> is the correct documentation right now.</p>

<p>Edit: you might want to call the <code>getRawSql()</code> 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</p>

<p><strong>Edit2:</strong> <br>
You don't need to read the following part, these are just some explanations to the <code>content.field_code</code>part - if you are interested in Craft go ahead if not skip it.</p>

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

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

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

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

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

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

CC BY-SA 3.0
2017-11-29T11:52:10.720Z
Thank you!. I also read the Yii documentation but didn't get it right.
Rosana Ruiz
CC BY-SA 3.0
2017-11-29T11:13:47.427Z
2017-11-29T13:45:19.763Z
2017-11-29T13:45:19.763Z
Robin Schambach
<p>How do you query an Element by a custom field in Craft 3? 
My first try, according to <a href="https://github.com/craftcms/docs/blob/master/en/element-queries.md" rel="nofollow noreferrer">craftcms/docs</a> was: </p>

<pre><code>$country = Entry::find()
                -&gt;section('countries')
                -&gt;where(['LIKE', 'code', 'CA'])-&gt;one()
</code></pre>

<p>But this throws an exception. Here <code>code</code> is a custom text field. I've also tried <a href="https://craftcms.stackexchange.com/questions/8587/filter-users-by-a-custom-field">some answers</a> on stack exchange and many other combinations but they're for Craft 2 and neither they work.</p>

<craft3><query>
How to query an entry by a custom field in Craft 3
# How to query an entry by a custom field in Craft 3

(Asked by: username_0 on 2017-11-29)

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.

**Tags:** craft3, query

---

## 2 answers

### Answer by username_2 on 2017-11-29. Score: 9 | Accepted answer

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

> **Comments:**
> **username_0**: Thank you!. I also read the Yii documentation but didn't get it right.  

---

### Answer by username_1 on 2019-12-20. Score: 3

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


---

