I have an add-on I've built to provide custom e-commerce reports for a client. It started as one of those 'we need this very specific thing' builds and of course has grown and grown as more needs arose. At it's core it is querying the db of an EE & Cartthrob site to pull out information about orders, products, etc.

A problem I've run into as it's grown is that it's not very portable between local/dev/live. My queries are built using field ID's, which is problematic if I end up with any discrepancies in when fields were created between those environments. This is a large & very actively developed site, so I'm not often able to wipe out my dev/local DB's with the live DB to correct those problems as they arise.

To solve the issue, I'd prefer to use field name's as opposed to field ID's, but I can't work out how to best integrate that into my queries. I know I can get ahold of field names, but from an add-on development / query standpoint I'm not sure how to best integrate them. Here's a trimmed down & anonymized version of one of my queries. I've removed a lot of fields & all field ID's are 00, which isn't the case in the real query obviously.

SELECT FROM_UNIXTIME(pict.entry_date, '%c/%e/%Y %r') as 'Order Date',
picd.field_id_00 as QTYShipped,
ocd.field_id_00 as 'email address',
ocd.field_id_00 as Company,
ocd.field_id_00 as Ship_Address1,
ocd.field_id_00 as Ship_Address2,
ocd.field_id_00 as Ship_City,
ocd.field_id_00 as Ship_State,
ocd.field_id_00 as Ship_Zip,
picd.field_id_00 as 'Order Number',
oct.status as 'Order Status',
pcd.field_id_00 as 'Product ID Number',
pm.member_id as 'Member ID',
pmg.group_title as 'Member Group'
from exp_channel_data as picd 
left join exp_channel_titles as pict on picd.entry_id = pict.entry_id 
left join exp_channel_data as ocd on picd.field_id_00 = ocd.entry_id 
left join exp_channel_titles as oct on ocd.entry_id = oct.entry_id
left join exp_channel_data as pcd on picd.field_id_00 = pcd.entry_id
left join exp_members as pm on oct.author_id = pm.member_id
left join exp_member_groups as pmg on pm.group_id = pmg.group_id
where picd.field_id_00 in (select entry_id from exp_channel_titles where channel_id = '0')
order by pict.entry_date desc

So the question is - in an add-on, what's the best way to feed this query from field names rather than field ID's?


You can use the channel fields api to get the field names:


$fields = ee()->api_channel_fields->fetch_custom_channel_fields();

//change 1 to your site_id if using MSM
$quantity_field_id = $fields['custom_channel_fields'][1]['your_quantity_field_name'];

$sql .= "picd.field_id_{$quantity_field_id} as QTYShipped,"

You can store your field id's as config vars, so you can change them per environment. Nice thing about this is you don't incur an extra query to get the field names.

In your main config.php

$config['your_addon_quantity_field'] = 3;

In your addon:

$quantity_field = ee()->config->item('your_addon_quantity_field');
$sql .= "picd.field_id_{$quantity_field} as QTYShipped,"
  • Nice, and you could make a settings view for your add-on and fetch the field names and ID's there and store them in the add-on settings (sadly probably base64), or just save them in config file.
    – GDmac
    Feb 21 '14 at 19:48

The best practice would be to have fields mapped in add-on settings. If you think that is overkill, store them in custom or global config file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.