SQL weight rows by formula

Question

I am trying to make a search form to find users.

How can I score the row based on the following formula?

score = isInTheSameCityWithMe + isInTheSameStateWithMe + isInTheSameCountryWithMe

So, if I live in Paris it should put the people that also live in Paris on the top.

My implementation is something like this

<code>$users = \DB::table('users')->
                    select('name', 'id', 'city', 'country', 'avatar');
if(\Auth::user()->hasLocation()) {
            $weightString = '';
            if(\Auth::user()->hasLocationFullToCity())
                $weightString = 'CASE WHEN users.city = \''.\Auth::user()->city.'\' THEN 4 ELSE
                    CASE WHEN users.state = \''.\Auth::user()->state.'\' THEN 3 ELSE
                    CASE WHEN users.country = \''.\Auth::user()->country.'\' THEN 2 ELSE 1 END END END';

            elseif(\Auth::user()->hasLocationFullToState())
                $weightString = 'CASE WHEN users.state = \''.\Auth::user()->state.'\' THEN 3 ELSE
                    CASE WHEN users.country = \''.\Auth::user()->country.'\' THEN 2 ELSE 1 END END';

            elseif(\Auth::user()->country)
                $weightString = 'CASE WHEN users.country = \''.\Auth::user()->country.'\' THEN 2 ELSE 1 END END';
            $users->addSelect(\DB::raw($weightString.' AS score'))->orderBy('score', 'DESC');
        }

Which give me a query like

select `name`, `id`, `city`, `country`, `avatar`, CASE WHEN users.city = 'Cluj Napoca' THEN 4 ELSE CASE WHEN users.state = 'Cluj' THEN 3 ELSE CASE WHEN users.country = 'Romainia' THEN 2 ELSE 1 END END END AS score from `users` where `name` LIKE ?  order by `score` desc

Is there a better way?

Answer 1

A single case expression is much simpler:

select `name`, `id`, `city`, `country`, `avatar`,
       (CASE WHEN users.city = 'Cluj Napoca' THEN 4 
             WHEN users.state = 'Cluj' THEN 3 
             WHEN users.country = 'Romainia' THEN 2
             ELSE 1
        END) AS score
from `users`
where `name` LIKE ? 
order by `score` desc

EDIT:

If you have a specific user in mind, then it would look like:

select u.name, u.id, u.city, u.country, u.avatar,
       (CASE WHEN u.city = u2.city THEN 4 
             WHEN u.state = u2.state THEN 3 
             WHEN u.country = u2.country THEN 2
             ELSE 1
        END) AS score
from `users` u join
     (select u.* from users u where u.name = $name) u2
     on u.name <> u2.name
where u.name LIKE ? 
order by score desc