0

I'm on EE2.10.1, and trying to grab unique countries from a channel that has one field for "country name" and one field for "country code". So I've got the following query:

{exp:query sql="SELECT DISTINCT field_id_47 AS country_code, field_id_46 AS country FROM exp_channel_data WHERE channel_id='4' ORDER BY field_id_46 ASC"}
    <option value="{country_code}">{country}</option>
{/exp:query}

... which works great, except: I need to exclude expired entries (but include future entries). I think it probably involves doing a JOIN on exp_channel_titles, but my SQL isn't really up to it!

Anyone know what the correct query should be? Thanks loads!

0

try something like this:

SELECT DISTINCT 
d.field_id_47 AS country_code, 
d.field_id_46 AS country 
FROM exp_channel_data d
LEFT JOIN exp_channel_titles t ON d.channel_id = t.channel_id
WHERE t.channel_id='4' 
AND ( t.expiration_date = 0 OR t.expiration_date >= UNIX_TIMESTAMP() )
ORDER BY d.field_id_46 ASC
| improve this answer | |
  • Thanks for posting this – unfortunately it didn't seem to work – it still outputs entries that have expired... And just to make things more complicated: I realise now that I also need to restrict it to entries that are related to a specific entry in another channel. So something along the lines of "WHERE d.field_id_36={some_entry_id}" – except that I see relationships are in their own db table too! Do you think this is possible? Thanks so much for your help. – Frank Harrison Jan 12 '16 at 16:02
  • Why don't you use the channel entries tag pair and all that comes with it, just do some housekeeping and get rid of the duplicates, then you can use the relationships as well. – Jim Wyse Jan 12 '16 at 18:54
  • Yes that's probably a good plan – I was hoping to come up with a super efficient query but it's probably overly complicated! Thanks for your help, much appreciated. – Frank Harrison Jan 12 '16 at 20:24

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.