SQL SELECT using custom field name (not ID) in WHERE

Question

Forgive me if I missing something obvious, but I have a need to select channel entries from the EE database, but I want to select those entries based on a value assigned to one of my channels custom fields. In the exp_channel_data table though I have no guaranteed way of knowing which column that data might be in due to how EE adds columns to the table. It could be field_id_27 or field_id_123. I could look in the admin, get the ID and then infer the column name, but that's clunky. Also, I'm trying to use this in a custom plug-in and I can't assume the ID will always be the same as I move the code from EE install to EE install. So what I want to know, and can't find, is what is the recommend method to build a SQL query in EE to be able to use a particular custom field in your WHERE clause?

To put it another way, if my custom field is named my_cutom_field what I want to do is be able to say:

WHERE my_custom_field = 'some value'

I hope this question makes sense.

Answer 1

Yes, you can prepare an array which would contain the custom field name and id like:

$fields = array();

$query = $ee()->db->query("SELECT exp_channel_fields.field_id, exp_channel_fields.field_name FROM exp_channel_fields
LEFT JOIN exp_channels ON exp_channel_fields.group_id=exp_channels.field_group 
WHERE exp_channels.channel_name='[CHANNEL NAME]'");

foreach($query->result_array() as $row){
    $fields[$row['field_name']] = 'field_id_'.$row['field_id'];
}

Now within the WHERE condition you can put like: WHERE $fields['my_custom_field '] = 'some value'

Answer 2

How about using a JOIN on exp_channel_fields which contains the field metadata?