0

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

2 Answers 2

0

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'
0
0

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

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.