Removing duplicate entries using an SQL query

Question

I'm trying to create a search field (using Solspace Super Search) using a location from existing channel entries. Unfortunately this results in duplicate locations as some entries use the same location.

I explored removing duplicates using javascript but this doesn't play nicely with Super Search so I've been advised to try using an SQL query.

Here's the code I currently have but it doesn't appear to be working as it's outputting loads of duplicates now rather than just a few.

<select id="locationList" name="club_feed_town">
    <option value="">All Locations</option>
    {exp:channel:entries 
        channel="friendship_clubs_data_feed" 
        dynamic="no" 
        status="Open|Exclude from search results" 
        orderby="club_feed_town" 
        sort="asc"
    }
        {exp:query sql="SELECT DISTINCT field_id_174 FROM exp_channel_data"}
            <option value="{club_feed_town}">{club_feed_town}</option>
        {/exp:query}
    {/exp:channel:entries}
</select>

Any ideas where I'm going wrong?

Thanks in advance,

Tom

Answer 1

I would have solved that with storing everything in a exp:stash:set_list, and then looping out it with a exp:stash:get_list.

Read more on how to do that here: https://github.com/croxton/Stash/wiki/%7Bexp%3Astash%3Aset_list%7D

Let me know if you need any code examples, I'd be happy to help out.

Answer 2

I've now solved this one by using an SQL Query as follows:

<select id="locationList" name="fieldName">
    <option value="">All Locations</option>
        {exp:query sql='
            SELECT DISTINCT field_id_174 
            AS fieldName 
            FROM exp_channel_data 
            WHERE field_id_number <> "" 
            ORDER BY fieldName
        '}
            <option value="{fieldName}" {if super_search_fieldName == "{fieldName}"}selected="selected"{/if}>{fieldName}</option>
        {/exp:query}
</select>