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