0

EE 3.1.4

I need to get a detail field from a grid field, but I also need the results to be distinct. The issue I have is the grid field returning a pipe delimited field set.

So running something like this:

{exp:query sql="SELECT DISTINCT field_id_23 AS bedroom_count FROM exp_channel_data WHERE channel_id=3"}
    {bedroom_count}
{/exp:query}

yields results similar to:

315,000|3|2|2|1|2374

...but be able to return the distinct values from a particular segment inside that set like just the field that, in this case, was '3'.

Is there a way to better do this? I need to stash them in a stash list to be used in a ddl selection.


* ANSWER *

I wish I could accept two answers for this. Derek provided what I asked for, but Stuart made me fix my original mistake.

I originally had this being done with stash. My mistake was that I had this:

{!-- BATHROOMS --}
{exp:channel:entries channel="house" dynamic="no" limit="999999"}
    {exp:stash:set_list name="bathroom_counts" parse_tags="yes" parse_depth="2"}
        {house_details}
            {stash:bathrooms}{house_details:bathrooms}{/stash:bathrooms}
        {/house_details}
    {/exp:stash:set_list}
{/exp:channel:entries}

And fetched it like this:

{exp:stash:get_list name="bathroom_counts" unique="yes"}
    <li>{bathrooms}</li>
{/exp:stash:get_list}

The problem I was having, besides being extremely tired at the time, was creating my list within the channel:entries loop.

I needed the following:

{!-- BATHROOMS --}
{exp:stash:set_list name="bathroom_counts" parse_tags="yes" parse_depth="2"}
    {exp:channel:entries channel="house" dynamic="no" limit="999999"}
        {house_details}
            {stash:bathrooms}{house_details:bathrooms}{/stash:bathrooms}
        {/house_details}
    {/exp:channel:entries}
{/exp:stash:set_list}
2
  • Does it need to be a query? Would channel entries/Grid tags be more purposeful? Mar 15, 2016 at 3:07
  • I've tried both ways... I think the CE and Grid tags would be better. Especially if I could use the "unique" tags for stash. I'm only getting a single result right now though. :(
    – W3bGuy
    Mar 15, 2016 at 3:18

2 Answers 2

0

Just to recap, you're capturing 'bathrooms' count from multiple entries with each bathroom count residing inside a Grid field, if correct, then using stash:

{exp:stash:set_list name="bathrooms" parse_tags="yes" parse_depth="2"}
    {exp:channel:entries…}
      {stash:url_title}{url_title}{/stash:url_title}
      {grid_field}
        {stash:bathroom}{grid_field:bathrooms}{/stash:bathroom}
        {stash:bedroom}{grid_field:bedroom}{/stash:bedroom}
      {/grid_field}
    {/exp:channel:entries}
{/exp:stash:set_list}

Basically we're capturing the URL, the bathroom & bedroom count which we can then use elsewhere on the page.

Assume we have 50 entries, we can use the unique column tag and single out unique bathrooms and only return 1 of each count:

{exp:stash:get_list name="bathrooms" unique="bathroom"}
    <li>{url_title}||Bedrooms: {bedroom}|| Bathroom: {bathroom}</li>
{/exp:stash:get_list}

Disclaimer: this is all untested code – it represents an approach.

1
  • I am accepting this answer because it pointed out my ID10T error. I updated my OP with the rest of the information. ;)
    – W3bGuy
    Mar 15, 2016 at 16:11
1

I preface the answer by saying that I do not recommend using SQL to get at your entry data this way, as it's what the Channel Entries tag and field variables are designed to do for you. That said, aggregation functions aren't currently available so I understand the desire.

Grid data is stored in a separate table for each Grid field. The data you see in the exp_channel_data table for that field is just a raw pipe-dump that is used by the Channel Entry tag's search:field= parameter when a Grid field is searchable. So if you are wanting to access the data directly, you need to use that Grid field's table.

SELECT DISTINCT col_id_1
FROM exp_channel_data AS cd
LEFT JOIN exp_channel_grid_field_23 AS gf ON gf.entry_id = cd.entry_id
WHERE cd.channel_id = 3

You will need to change col_id_1 to the actual column in your Grid field table for the column you are targeting.

1
  • Derek, thanks for the answer. I agree that accessing the data in this manner is definitely not desirable. I would like to be able to use the channel:entries loop and the grid:field to build a list that I can store in stash and then extrapolate using the "unique" flag in stash. I think that would work, but I'm having issues getting that together for some reason at the moment.
    – W3bGuy
    Mar 15, 2016 at 3:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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