Howto get a list of distinct occurences of certain metadata?

Hi all, 

I would like to fetch a list of distinct occurences of for example CountryNames from my groups.

for example:

Group,Title,Countryname
1,'one','France'
2,'two','France'
3,'three','France'
4,'four', 'Netherlands'
5,'five', 'Netherlands'
6,'six', 'Belgium'

Should return:
'Belgium'
'France'
'Netherlands'

Any ideas of how to accomplish this?
I need this to be able to make a drill-down search. 

  • In sql this would be something like:

    select distinct ms.id, ms.string
    from elgg_metadata md
    join elgg_metastrings ms on ms.id = md.value_id
    join elgg_metastrings ms2 on ms2.id = md.name_id
    where ms2.string = 'CountryName'
    order by ms.string

    But how would i go about doing this with elgg's api calls?

  • Excellent, exactly what i needed! 

    Thanks Cash

  • Ok, so i now have my list of CountryNames!
    With this list i'd like the user to select a CountryName and present him the next part of the user search: a list of distinct occurrences of ProvinceNames (also a piece of MetaData of my groups). The user would then select the ProvinceName and finally would be presented all groups with the selected CountryName and ProvinceName.

    Could anyone please elaborate on how to accomplish the second part (the retrieval of the distinct ProvinceNames from my groups, with CountryName as parameter)?