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