Using {paginate} in a {parents} pair tag

Question

I'm using EE 7.3.9 have 2 channels related via a relationship field, and I would like to create a page showing all the entries in channel 1 (the parent) that have in common the same field in the channel 2 (the child).

So I created a list with

{exp:channel:entries channel="channel2" url_title="{segment_2}" limit="1"}
{parents field="relationship_field" limit="4" paginate="bottom"}
{parents:title}
{paginate}
        <p>Page {current_page} of {total_pages} pages {pagination_links}</p>
{/paginate}
{/parents}
{/exp:channel:entries}

It shows the first 4 entries that have that particular field in {segment_2}, but the pagination doesn't show.

I also tried to achieve the same result in the other way, accessing via the parent field

{exp:channel:entries channel="channel1" search:relationship_field="{segment_2}" limit="4" paginate="bottom"}

but it doesn't show anything, and in the documentation there is this statement

"Relationship fields cannot be searched using the search parameter".

So, how can I achieve the goal to show all the entries that have in common a particular relationship field AND the pagination?

Thank you in advance for your answer.

Answer 1

This might be a good excuse to use a custom query combined with a template embed. In short, you use the query to get a list of entry_ids based on your criteria. Then pass it to the embed template to output and paginate:

First the custom query

{!-- get related parent entry IDs for the given child entry --}
{exp:query sql="SELECT parent_id 
               FROM exp_relationships 
               WHERE child_id = 
                 (SELECT entry_id FROM exp_channel_titles WHERE url_title = '{segment_2}' LIMIT 1)"}

    {!-- if it's the first row, set a variable with the entry ID. For subsequent rows, append the ID with a pipe. --}
    {if count == 1}{embed="your_template_group/related_entries" the_ids="{parent_id}"}{if:else}|{parent_id}{/if}
{/exp:query}

and the template embed:

{exp:channel:entries channel="channel1" entry_id="{embed:the_ids}" limit="4" paginate="bottom"}
    {title}
    {paginate}
        <p>Page {current_page} of {total_pages} pages {pagination_links}</p>
    {/paginate}
{/exp:channel:entries}