How to compare multiple fields using SQL?

Question

I am creating a recommendation system using Laravel php and sql. The user enters values during registration and they are stored in the user table.

Here is my users table schema , any field that has the recommendation name format e.g recommendationLocation is the location of the user the system should search for.

TABLE `users` (
  `id` (int),
  `username` (varchar),
  `email` (varchar),
  `password` (varchar),
  `age` (varchar),
  `location` (varchar),
  `country` (varchar),
  `userType` (varchar),
  `religion` (varchar),
  `variable1` (varchar),
  `variable2` (varchar),
  `recommendationAge` (varchar),
  `recommendationLocation` (varchar),
  `recommendationReligion` (varchar),
  `recommendationVariable1` (varchar),
  `recommendationVariable2` (varchar),
  `recommendationUserType` (varchar),
  `recommendationHairColor` (varchar),
  `recommendationCountry` (varchar),
)

The below statement would be a perfect straight forward user match but not all users will enter the identical information. My problem is that there are a lot of different variables that I have to consider as there are a high amount of combinations. As I want to be able to still find a user even if the user doesn't have certain matching info. For example, The statement could only have one matching field but the query still returns the username with this matching field.

$recommendationQuery = DB::table('users')
            ->select('username')
            ->where('id', '!=', Auth::id()) // Can't be the  current Auth User Details 
            ->where('age', '=' , Auth::user()->recommendationAge) // This is a required where clause for all statements 
            ->where('location', '=', Auth::user()->recommendationLocation) // This is a required where clause for all statements 
            ->where('religion', '=', Auth::user()->recommendationReligion)
            ->where('variable1', '=', Auth::user()->recommendationVariable1)
            ->where('variable2', '=', Auth::user()->recommendationVariable2)
            ->where('age', '=', Auth::user()->recommendationAge)
            ->where('location', '=', Auth::user()->recommendationLocation)
            ->where('country', '=', Auth::user()->recommendationCountry)
            ->where('hairColor', '=', Auth::user()->recommendationHairColor)
            ->get();

Are there any alternative and more efficient ways? thanks

crishoj · Answer 1 · 2019-02-03T18:18:33.193

As stated, the way to allow for matches on a single property is to use a logical disjunction (OR):

DB::table('users')
  ->select('username')
  ->where('id', '!=', Auth::id())// Can't be the  current Auth User Details
  ->where('age', '=', Auth::user()->recommendationAge)// This is a required where clause for all statements 
  ->where('location', '=', Auth::user()->recommendationLocation)// This is a required where clause for all statements 
  ->where(function ($query) {
        $query->where('religion', '=', Auth::user()->recommendationReligion)
              ->orWhere('variable1', '=', Auth::user()->recommendationVariable1)
              ->orWhere('variable2', '=', Auth::user()->recommendationVariable2)
              ->orWhere('age', '=', Auth::user()->recommendationAge)
              ->orWhere('location', '=', Auth::user()->recommendationLocation)
              ->orWhere('country', '=', Auth::user()->recommendationCountry)
              ->orWhere('hairColor', '=', Auth::user()->recommendationHairColor)
    })
  ->get();

The ->where(function ($query) { ... }) notation is Eloquent's way to introduce parenthesis in the resulting SQL query. These parenthesis are required, since AND takes precedence over OR.

As a more elegant approach, consider moving the properties used for matchmaking to a secondary table instead:

TABLE `user_properties` (
  `id` (int),
  `user_id` (int)
  `property` (varchar),
  `value` (varchar),
  `recommendation` (varchar),
  UNIQUE KEY(user_id, property)
)

Storing the properties in a column-oriented way will allow you to elegantly construct a query to find all cases where a property recommendation matches the value of another user:

SELECT other_user.user_id, user.property, user.recommmendation, other_user.value
FROM user_properties user
JOIN user_properties other_user
  ON other_user.property = user.property
  AND other_user.value = user.recommendation
  AND other_user.user_id != user.other_id
WHERE user.user_id = ?

(? here is a placeholder for the current user, for whom you wish to find matches.)

You could then count the number of matching properties for each other user, and use this tally as a score, and pick the N best matches:

SELECT other_user.user_id, COUNT(*) AS score
FROM user_properties user
JOIN user_properties other_user
  ON other_user.property = user.property
  AND other_user.value = user.recommendation
  AND other_user.user_id != user.other_id
WHERE user.user_id = ?
GROUP BY other_user.id
ORDER BY score DESC
LIMIT 10

hi, thanks for the reply. I'm new to anything this complex but I understand your answer however have u any examples of php of how I may do this and also how to find the number of matching properties. Also what SQL would be used to get the returning username? — John, Feb 03 '19 at 17:52
Thanks for both suggestions , currently doing some research on this. using Logical disjunction would work however as the table grows and i implement more fields this will get very time consuming. I think column oriented user properties would be the way. however i'm a bit confused how the statement will loop through the column as it will be separated with commas ? — John, Feb 03 '19 at 18:52
I may need to make mandatory and optional conditions to simplify this system — John, Feb 03 '19 at 18:55
What exactly is separated separated by comma? As for optional and mandatory conditions, a further refactoring to support this could be to have a third table containing the definition for each user property, including whether match is mandatory. — crishoj, Feb 04 '19 at 05:24

score 1 · Accepted Answer · answered Feb 03 '19 at 18:41

Currently you code produces something like the following SQL :

SELECT username
FROM users
WHERE 
    id =! ? AND age = ? AND location = ?
    AND religion = ? AND variable1 = ? AND ...

For a user to match, it needs to fufill all conditions in the WHERE clause.

From my understanding you are trying to separate mandatory conditions from optional conditions, like :

SELECT username
FROM users
WHERE 
    id =! ? AND age = ? AND location = ?
    AND (
       religion = ? OR variable1 = ? OR ...
    )

With this query, a user needs to match the 3 mandatory conditions on id, age and location, and any of optional conditions (religion, variablbe1, ...).

To achieve this, you can leverage Lavarel parameter grouping, like :

$recommendationQuery = DB::table('users')
    ->select('username')
    ->where('id', '!=', Auth::id()) // Can't be the  current Auth User Details 
    ->where('age', '=' , Auth::user()->recommendationAge) // This is a required where clause for all statements 
    ->where('location', '=', Auth::user()->recommendationLocation) // This is a required where clause for all statements 
    ->where(function ($query) {
        $query->where('religion', '=', Auth::user()->recommendationReligion)
            ->orWhere('variable1', '=', Auth::user()->recommendationVariable1)
            ->orWhere('variable2', '=', Auth::user()->recommendationVariable2)
            ->orWhere('age', '=', Auth::user()->recommendationAge)
            ->orWhere('location', '=', Auth::user()->recommendationLocation)
            ->orWhere('country', '=', Auth::user()->recommendationCountry)
            ->orWhere('hairColor', '=', Auth::user()->recommendationHairColor)
        })
        ->get();

Parameter grouping would work however it would be a lot of work as the recommendation system scales — John, Feb 03 '19 at 18:54
OR function may be the best and simplest option with group parametering , I will try this solution also . THanks — John, Feb 03 '19 at 18:58

How to compare multiple fields using SQL?

2 Answers2