2

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.

0
2

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.

1
  • No there's a problem well solved and ready to roll for the next time. Very tidy. :) – Ian Apr 12 '13 at 18:51
4

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.

2
  • This is amazing - love that I have this tool now. – Derek Hogue Apr 9 '13 at 19:34
  • Low's example is awesome, but didn't concatenate the ids for me until I removed the last GROUP BY entry id portion of the sql statement. In case anyone else runs into the query looping instead of concatenating. – Romans-8---31-39 May 31 '13 at 21:00
0

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}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.