Order By Similarity?

Yii 1.1 General Discussion
1

Hey guys!

I have a big database of patients. I’m querying the database for those patients based on their first and last names, or partial names. I want to order the returned list of patients by their similarity to the search criteria, but I’m having some trouble figuring it out. Here’s what I’m working with so far:


$names = explode(' ', $this->input);

		

		

	if (count($names) === 1 ){

		$command=Yii::app()->db->createCommand()

			->select('*')

			->from('Patient')

			->where("firstName=(:input)")

			->orWhere("lastName=(:input)")

			->orWhere('firstName like :input', array(':input'=>"%{$this->input}%"))

			->orWhere('lastName like :input', array(':input'=>"%{$this->input}%"));

		

		$command->bindParam(':input', $this->input, PDO::PARAM_STR);

	} else {

		$command=Yii::app()->db->createCommand()

			->select('*')

			->from('Patient')

			->where("firstName=(:firstName)", array(':firstName'=>$names[0]))

			->orWhere("lastName=(:lastName)", array(':lastName'=>$names[1]))

			->orWhere('firstName like :firstName', array(':firstName'=>"%{$names[0]}%"))

			->orWhere('lastName like :lastName', array(':lastName'=>"%{$names[1]}%"))

			//->order()

                        ;

	}

I’m pretty new to both sql and yii (a few months with each). I’m sure this isn’t the most elegant way to do this, but it works for my purposes. My group doesn’t really want to use active record as we’re more familiar with straight sql (shame that…), and we’re running short on time.

Any suggestions?

2

You need this.

Example of usage.

3

I’m using InnoDB in MySQL 5.5, which doesn’t support fulltext. I really don’t want to switch my engine to MyISAM. Is there any other way to do this?

4

Well, you can order by conditions used in WHERE (for example, ORDER BY first_name = … DESC, last name = …).

Don’t think it’s a good idea, though.

5

Have you considered Sphinx? I think it would be a wise decision to use it, especially if you have a large db and need to make such specific searches.

6

There is the SOUNDEX() function which you can sort by. Another option were the levenshtein distance. But I cannot imagine both options to scale well. Perhaps you should go with Sphinx or a similar external search engine as Yugene suggested.