[eluser]Andy UK[/eluser]
I'm sorry to say that i'm very confused right now. In the hope that someone might be able to point me in the right direction, let me try to explain in a bit more detail...
I'm calling a function in my model that returns properties which may or may not be filtered by things such as price, city, neighbourhood, property type, features, etc. Values such as price are in the property table. Other items such as neighbourhood, property type, etc are in separate tables, with just the ids being stored in the property table.
Now features are a bit more complicated. A property may have many features, so i had to add a join table with a column for property ids and a column for feature ids, with the features of course being in another table.
Here's the rub... I'm trying to filter the results by selecting features which are added to an array. For example, i might click on 'fireplace' which has a feature id of 6, so the feature array now looks something like this: (0 => 6). If i were to add another feature, say for a furnished property, then that would be pushed onto the array to give us (0 => 6, 1 => 34).
In my query i have the following code to check if a feature exists or not:
Code:
->join('feat_prop_join', 'property.id = feat_prop_join.property_id', 'left')
->where_in('feat_prop_join.feature_id', $this->session->userdata('selected_features'))
Obviously this is not working because instead of showing properties that have both features 6 and 34, it is giving me properties that have either one of 6 or 34. As I add more features, i need the filter to become MORE restrictive, not less.
In another part of the query i'm using the following code to get a csv of the features that apply to each property. This is used in a different section of code, but i'm not sure if it's relevant here:
Code:
->select('group_concat(feat_prop_join_concat.feature_id) as feature_id_concat', FALSE)
->join('feat_prop_join as feat_prop_join_concat', 'property.id = feat_prop_join_concat.property_id', 'left')
Basically i just explode this string into an array later on and use it to adjust the feature count. A foreach loop runs through all the possible features and an inside loop runs through the resultset of properties and if the feature id appears in the exploded array, then the count is bumped up by one.
Now what i'm really stuck on is how to make the filter inclusive. By that i mean having it match all the values in the user defined array of active features to the list of features available for each property. As you can see in my previous post i tried a foreach loop which would have translated to something like this:
Code:
->where('feat_prop_join.feature_id', 6)
->where('feat_prop_join.feature_id', 34)
But that was no good. I ended up getting zero results once i added a second feature to the array.
I guess in some way what i'm trying to achieve is a code which says the values must appear in both arrays for the property to be selected. In quasi-code something like...
where_in($feature_id_concat, $active_features_array)
Or in other words, where the feature ids in the active features array all appear in the concatenated list of features for that property.
Here's an excerpt of a raw resultset of two properties so you can see what i mean by the feature_id_concat...
Array
(
[0] => stdClass Object
(
[id] => 259
[city_id] => 1
[barrio_id] => 9
[property_type_id] => 1
[transaction_type] => 1
[currency_id] => 1
[price] => 3200000000
[sale_price] => 3200000000
[beds] => 5
[baths] => 5
[area] => 495
[feature_id_concat] => 1,2,4,5,6,9,15,16,20,21,24,27,31,35,37,38
)
[1] => stdClass Object
(
[id] => 606
[city_id] => 1
[barrio_id] => 100
[property_type_id] => 2
[transaction_type] => 1
[currency_id] => 1
[price] => 3364000000
[sale_price] => 3364000000
[beds] => 3
[baths] => 6
[feature_id_concat] => 1,2,3,4,5,6,8,15,16,18,21,28,29,31,34,35
)
)
I really hope someone can shed some light on this. If i could figure out where i'm going wrong, then at least i could work on a solution, but i'm a bit lost right now!
Thanks in advance for your patience in reading all this and any help is much appreciated.
