Can you query a grid field to return a list of distinct fields?

Question

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}

Answer 1

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.

Answer 2

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.