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?