Need help with a query to get data

Question

I need a complex query and have gone beyond my scope of understanding.

Here is the gist...

There is a section of the site in which users can select various values available in entries of a particular channel. Some are 'Categories', some are 'Grid Fields', Lastly, one is a range.

I pass the variables to a template displaying the results and get the values selected using Mo' Variables by Rob Sanchez.

So the URL will look something like this:

/find-your-home/search?city=edmond&sub-division=stonebriar&school-district=edmond-public-schools&Bedrooms=4&Bathrooms=2&min=90000&max=400000

I can get the variables like this:

{!-- Get the key values from the query string --}
{exp:stash:search_city}{get:city}{/exp:stash:search_city}
{exp:stash:search_sub-division}{get:sub-division}{/exp:stash:search_sub-division}
{exp:stash:search_school-district}{get:school-district}{/exp:stash:search_school-district}
{exp:stash:search_bedrooms}{get:Bedrooms}{/exp:stash:search_bedrooms}
{exp:stash:search_bathrooms}{get:Bathrooms}{/exp:stash:search_bathrooms}
{exp:stash:search_min}{get:min}{/exp:stash:search_min}
{exp:stash:search_max}{get:max}{/exp:stash:search_max}

So far, so good...

So the houses are in their own channel so I want to query that id: channel_id = '3' (In this case)

The following are categories: city, sub-division, and school-district

I then have a grid field for details which is listed as comma separated values. This is where the price, bedrooms, and bathrooms are kept.

So basically, in the table exp_channel_data:

entry_id, and field_id_23 seem to be the only fields I need.

field_id_23 looks like this: 349900|4|3|1|2|0|3|2557

In this instance, 349900 is the price that would need to fall between the min and max range passed.

Then the next two segments, 4 and 3 are the Bedrooms and Bathrooms respectively.

Then I will need the exp_category_posts table to get the categories for the entries. I should be able to join the two tables on the entry_id.

The category group I need to get is group 2. (If that matters)

The City category is a parent with an ID of '2'. Under this are (currently 4) child categories of which Edmond has an ID of 9.

The Sub-Division category is a parent with an ID of '1'. Under this are (currently 6) child categories of which Stonebriar has an ID of 8.

The School Districts category is a parent with an ID of '16'. Under this are (currently 3) child categories of which Edmond Public Schools has an ID of 17.

I have tried to put this together, but I don't know the best way to get it all together. It would be nice to be able to pass it through a {exp:channel:entries} loop.

If I could get all of this filtered with the above and return a group of ids to run, I think it would work.

Something like this: SELECT GROUP_CONCAT(DISTINCT exp_channel_data.entry_id SEPARATOR '|') AS entry_ids FROM exp_channel_data WHERE channel_id = '3' only WITH the other query data filters applied as this one returns all the ids in that channel.

---

EDIT

I updated the drop downs to populate the categories with the category_id as the values to pass. This should simplify the category issue in the query. :)

So the URL will look like this now:

/find-your-home/search?city=9&sub-division=8&school-district=any&Bedrooms=4&Bathrooms=3&min=90000&max=400000

Answer 1

This is the MSSQL query I came up with, but it looks like you're using MySQL so you'll have to convert a few things.

declare @search_city varchar(50) = 'edmond'
declare @search_subdivision varchar(50) = 'stonebriar'
declare @search_schooldistrict varchar(50) = 'edmond-public-schools'

select (select distinct ltrim(str(cd.entry_id)) + '|'
    from exp_channel_data cd
    inner join exp_category_posts cp on cd.entry_id = cp.entry_id
    where cd.channel_id = 3
          and (cp.category_id = 2 and cp.searchName = @search_city
               or cp.category_id = 1 and cp.searchName = @search_subdivision
               or cp.category_id = 16 and cp.searchName = @search_schooldistrict)
    --order by cd.entry_id
    for xml path(''), type
   ).value('.', 'varchar(200)') as 'entry_ids'

Answer 2

If anyone has a cleaner way of doing this, I'm all ears. :)

Ok, I was able to do this using nested queries with some of the MySQL string functions and checking for the IDs inside the looped results like this:

{exp:query sql="
    SELECT GROUP_CONCAT(DISTINCT exp_category_posts.entry_id SEPARATOR '|') as query_houses
    FROM exp_category_posts
    WHERE exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_channel_data.entry_id
        FROM exp_channel_data
        WHERE exp_channel_data.channel_id = 3
    ) AND exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_category_posts.entry_id
        FROM exp_category_posts
        WHERE exp_category_posts.cat_id = {get:city}
    ) AND exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_category_posts.entry_id
        FROM exp_category_posts
        WHERE exp_category_posts.cat_id = {get:sub-division}
    ) AND exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_category_posts.entry_id
        FROM exp_category_posts
        WHERE exp_category_posts.cat_id = {get:school-district}
    ) AND exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_channel_data.entry_id
        FROM exp_channel_data
        WHERE SUBSTRING_INDEX(exp_channel_data.field_id_23, '|', 1) BETWEEN {get:min} AND {get:max}
    ) AND exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_channel_data.entry_id
        FROM exp_channel_data WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(exp_channel_data.field_id_23, '|', 2), '|', -1) >= {get:Bedrooms}
    ) AND exp_category_posts.entry_id IN (
        SELECT DISTINCT exp_channel_data.entry_id
        FROM exp_channel_data
        WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(exp_channel_data.field_id_23, '|', 3), '|', -1) >= {get:Bathrooms}
    )"
}
    {query_houses}
{/exp:query}

Which, in THIS instance I ran resulted in the following that I can pass to the {exp:channel:entries} tag as the entry_id parameter.

30|35|37|38