• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
require all values in array to be matched when using where_in

#1
[eluser]Andy UK[/eluser]
Hi,

I have an array of active features which i'm using to filter a result set. The array is stored in an environment variable and each time a user selects a feature, the results are filtered and the filter options are also reduced to reflect which ones are now available. For example when searching for a property the following filters might be available:

Furnished (27)
Fireplace (6)

If i choose the Fireplace filter, we might get a new set of feature options like this:

Furnished (5)
Fireplace (6)

Because there are only 5 furnished properties that also have a fireplace.

The features array will now have one value in it which is that of the fireplace feature id. However, if I also select Furnished, i'm back to where i started with the following:

Furnished (27)
Fireplace (6)

This is because i'm using where_in to check if the values are in the features array. What i really want is for all the values in the array to be matched and not just one or another. The line in question is:

Code:
$this->db->where_in('feat_prop_join.feature_id', $this->session->userdata('selected_features'));

Am I right in thinking that where_is is satisfied just as long as one of the values appears in the array? What can I use to require all the values to be matched?

Many thanks!

#2
[eluser]Andy UK[/eluser]
I guess i could run a foreach loop with just a standard where, but is there a more elegant or correct way to do this?

#3
[eluser]Andy UK[/eluser]
I tried the following, but after adding a second value to the array, i'm getting no results. Even though both feature ids appear together for a number of properties:

Code:
foreach($this->session->userdata('selected_features') as $feature)
   {
    $this->db->where('feat_prop_join.feature_id', $feature);
   }

#4
[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.

#5
[eluser]Andy UK[/eluser]
Here is the query for what it's worth...

Code:
$this->db->select("property.id,
       property.city_id,
       property.barrio_id,
       property.property_type_id,
       property.transaction_type,
       property.currency_id,
       property.{$price} as price,
       property.sale_price,
       property.rental_price,
       property.beds,
       property.baths,
       property.area,
       barrios.barrio")
    ->select('group_concat(feat_prop_join_concat.feature_id) as feature_id_concat', FALSE)
    ->join('feat_prop_join', 'property.id = feat_prop_join.property_id', 'left')
    ->join('feat_prop_join as feat_prop_join_concat', 'property.id = feat_prop_join_concat.property_id', 'left');
  
  // Features Filter
  if(is_array($this->session->userdata('selected_features')) && count($this->session->userdata('selected_features')) > 0)
  {
   $this->db->where_in('feat_prop_join.feature_id', $this->session->userdata('selected_features'));
  }

  $this->db->where('published', '1');
  $this->db->group_by('property.id');
  $this->db->distinct();
  $this->db->from('property');
  $result_array['all'] = $this->db->get();
  $result_array['num_rows'] = $result_array['all']->num_rows();

  return $result_array;

#6
[eluser]CroNiX[/eluser]
WHERE ... IN is a mysql function, and it only requires one match in the array.

#7
[eluser]CroNiX[/eluser]
You can probably do it using a HAVING clause in addition to your IN clause. See the top answer for this post:
http://stackoverflow.com/questions/11636...-in-clause

Not sure how you'd do it with Active Record.

It'd be something like:
PHP: $quantity = count($this->session->userdata('selected_features'));
SQL: HAVING COUNT(DISTINCT feat_prop_join.feature_id) = $quantity

#8
[eluser]Andy UK[/eluser]
Thanks for the reply CroNiX,

I see where you're going with the proposed solution, although i'm not sure how to implement it either. Since I can build the query with php, is there any reason i can't do the foreach loop i mentioned before? It's not working when the the filter array has more than one value in it though...

Code:
// Features Filter
  if(is_array($this->session->userdata('selected_features')) && count($this->session->userdata('selected_features')) > 0)
  {
   foreach($this->session->userdata('selected_features')) as $feature)
   {
   $this->db->where('feat_prop_join.feature_id', $feature);
   }
  }

Which would translate to multiple where clauses like this (assuming three filter values are chosen):

Code:
->where('feat_prop_join.feature_id', 6)
->where('feat_prop_join.feature_id', 34)
->where('feat_prop_join.feature_id', 23)

I mean it's not working as it is, but could this be a solution with some adjustment? I have a lack of understanding as to how SQL treats many to many relationships, since 'feat_prop_join.feature_id' could be any number of different feature ids. If a property has, say 17 features in the join table, can 'feat_prop_join.feature_id' be any one of those 17 values at once when running the query? If that's the case then i don't understand why having multiple where statements doesn't work.

Using the above example, say a property has features of ids 3, 6, 32, 34, 23, 10. Wouldn't the above code return this property as part of the result set? Again, what's confusing me is the fact that this is a many to many relationship and i've never had to deal with something like this before.

Thanks again

#9
[eluser]Andy UK[/eluser]
I'm very pleased to say i got it working after playing around with the solution CroNiX provided. Here is the active record/MySQL code:

Code:
// Features Filter
  if(is_array($this->session->userdata('selected_features')) && count($this->session->userdata('selected_features')) > 0)
  {
   $this->db->where_in('feat_prop_join.feature_id', $this->session->userdata('selected_features'));
   $this->db->having('count(distinct feat_prop_join.feature_id) = '. count($this->session->userdata('selected_features')), NULL, FALSE);
  }

The records are already grouped by the property.id further down


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.