Only outputting the same article once using relationships

Question

I have the following code:

<ul>
  {exp:channel:entries channel="activity"}
    {related_entries id="activity_location"}
    <li><a href="{url_title_path='locations'}">{title}</a></li>
    {/related_entries}
  {/exp:channel:entries}
 </ul>

This outputs all activities (activity) that have an activity location (activity_location is the channel field with the relationship), so I get multiple results which is fine, but a lot of these multiple results are the same. I want to only output one of each.

For example currently I'll get:

England, Wales, England, England, Wales, Scotland

I want it to simply output:

England, Wales, Scotland

Is there any easy way of doing this?

Answer 1

Unless this is a really simple site I would highly recommend switching to Playa relationships - you'll have lots more flexibility as the site grows. However, if you want to stick with native relationships, try using the unique parameter offered by Stash's get_list. Something like this works, whilst having the added benefit of keeping your markup cleanly separated from the data fetching part:

{exp:stash:set_list name="locations" parse_tags="yes"}
  {exp:channel:entries channel="activity"}
    {related_entries id="activity_location"}
        {stash:location}{title}{/stash:location} 
        {stash:url}{url_title_path='locations'}{/stash:url}                       
    {/related_entries}
  {/exp:channel:entries}
{/exp:stash:set_list}

<ul>
  {exp:stash:get_list unique="yes" name="locations"}
     <li><a href="{url}">{location}</a></li>
  {/exp:stash:get_list}
</ul>

Answer 2

Updated: The solution I first posted (below) was for the unique entries that match 1 parent entry. This query should pull all unique entries for your channel:

<ul>
  {exp:channel:entries channel="activity"}
    {exp:query sql="Select distinct t.title related_title, t.url_title related_url_title
                    from exp_relationships r join exp_channel_titles t
                    on r.rel_child_id = t.entry_id
                    where rel_parent_id in (Select entry_id from exp_channel_titles where channel_id = 1)"}
    <li><a href="/locations/{related_url_title}">{related_title}</a></li>
    {/exp:query}
  {/exp:channel:entries}
</ul>

Replace channel_id=1 with your channel's ID. Or use:

Select distinct t.title related_title, t.url_title related_url_title
                        from exp_relationships r join exp_channel_titles t
                        on r.rel_child_id = t.entry_id
                        where rel_parent_id in (Select entry_id from exp_channel_titles tt join exp_channel w
                        on tt.channel_id = w.channel_id where w.channel_name = 'activity')

Hopefully that works. The only site I have handy that uses native relationships is a 1.6.x site and the table/field names are different, so I'm writing the queries one way and then fixing them.

As was suggested in another answer, Playa offers many more options for this sort of thing, and conversion instructions.

First posting, that pulled related entries for 1 entry.

<ul>
  {exp:channel:entries channel="activity"}
    {exp:query sql="Select t.title related_title, t.url_title related_url_title
                    from exp_relationships r join exp_channel_titles t
                    on r.rel_child_id = t.entry_id
                    where rel_parent_id = {entry_id}"}
    <li><a href="/locations/{related_url_title}">{related_title}</a></li>
    {/exp:query}
  {/exp:channel:entries}
</ul>

The exp_relationships table is pretty straightforward.