I Would Like To Query A Range Of Criteria On Multiple Columns In Mysql

Yii 1.1 General Discussion
1

I have 3 columns in a mysql table. I’m using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :

We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int I have 1000 records in the table, as an example, I list 5 here.


Fast  |  smart  |  Sharp

------------------------

0         1          1

1         1          1

0         0          1

1         1          1

1         0          0

and let’s say we wanna search for people who are smart and sharp as 0 1 1 but if there is no record with the value of 0 1 1 we want to get the closest to it which is 1 1 1 or 1 0 1 or etc. So now when I search for it, I get the exact value, if the value doesn’t exist, I won’t get anything back from DB. So how can I achieve this and if I get a bunch of result, how can I sort them from the closets to my query to the feartest ?

2

Hi manhooei,

I would fetch the 4th column for sorting, probably named ‘distance’ … a virtual attribute calculated from the given conditions.

Something like this:




$distance_select = "(abs(t.fast - :fast) + abs(t.smart - :smart) + abs(t.sharp - :sharp))";

$criteria->select = array(

    "$distance_select as distance",

    "*",

);

$criteria->params = array(

    ":fast" => $this->fast;

    ":smart" => $this->smart;

    ":sharp" => $this->sharp;

);

$criteria->sort = "distance asc";

Not tested. And I’m not sure whether the parameter bindings work for “select” or not.

3

Thank you for sharing this solution, but can you please explain what you are trying to do here ?

4

Well, the code is just a fragment and won’t work by itself. It is just retrieving all the record without filtering, but with sorting by the “distance” to the given conditions of ‘fast’, ‘smart’ and ‘sharp’.

Do you want the close items only when the exact matches are not found? I think it’s very difficult to accomplish with a single query.

5

Yeah. I want to get anything close to the actual query if the exact match isn’t found. For example If I query for 1 0 1 and there is no record to match that, some other records liek 1 1 1, 1 0 0 and 0 0 1 for example return. I’m not sure if that is something easy to do. Any idea ?

6

Hmm, I would use 2 queries: one with exact matching first, and then the other with sorting by the closeness if necessary, just using simple "if else" in the PHP level.

Or rather I would be satisfied with using only the 2nd one … I’m a lazy programmer.

7

I decided to use Vector Space Model. It’s a bit complicated to implement and learn but it gives you exact result and approximate. Hope it helps you too. Here is the link :

8

Thank you for the link. :)

But it looks too gorgeous for a set of 3 integers. The simple arithmetic calculation would be enough for your current scenario. I’m a lazy programmer (once again).