Passing ID’s from a query to Channel Entries

Question

I'm using quite a complex query using exp:query to generate the member ID's of members who have certain data in a custom field.

I'm struggling on how to pass the generated ID's onto a channel entries tag.

I gather that I can't put a channel entries tag inside the query, it certainly didn't work correctly she. I tried, so should I be using php?

I've virtually zero knowledge with php, so does anyone have any suggestions on the correct way to pass a pipe delimited list of member ID's from a query?

Thanks.

Answer 1

It occurred to me this would be a great idea for a plugin, since it's something most people have to do often when building even moderately complicated sites, and currently requires you to understand both MySQL GROUP_CONCAT and the intricacies of the EE parse order.

So without further ado, I present the Channel Query plugin. Use it like so:

{exp:channel_query sql="SELECT entry_id FROM exp_channel_titles"}
    <!-- normal channel entries tag data -->
{/exp:channel_query}

The sql parameter specifies the custom SQL query. Only SELECT statements can be used, INSERT, UPDATE and DELETE are not supported for security reasons.

All other parameters and variables are exactly the same as the standard EE Channel Entries Tag.

If an orderby parameter is specified, that will take precendence. Otherwise, entries will be displayed in the order returned by your SQL query.

Answer 2

GROUP_CONCAT is your friend. Instead of returning multiple rows of entry IDs, GROUP_CONCAT lets you return a single row containing a concatenated string of a single field, like entry_id, like in this example:

{exp:query sql="SELECT GROUP_CONCAT(DISTINCT entry_id SEPARATOR '|') AS entry_ids
                FROM exp_channel_titles WHERE channel_id = 1
                # ...and more where clauses here...
                GROUP BY entry_id"}

    {exp:channel:entries entry_id="{entry_ids}"}
        ...
    {/exp:channel:entries}

{/exp:query}

So, use GROUP_CONCAT and SEPARATOR '|' to generate the pipe-separated list of field values of any field you want.

Answer 3

An embed solves this problem. You didn't provide any code in your question so my code below is for demonstrating the concept only. You'll need to adjust for what you need.

In your main template add your query module code like this:

{embed="embeds/member" ids="{exp:query sql='SELECT member_id FROM exp_members'}{member_id}|{/exp:query}"}

In your embed you can call the IDs like this:

{exp:channel:entries channel="news" limit="10" author_id="{embed:ids}"}
    {title}
{/exp:channel:entries}