Laravel DB Query with multiple sub strings

Question

I am looking to run a a DB query where if a user queries with multiple sub-strings across two columns they can bey queried. For example,

Column 1 is Country Name and Column 2 is City and Country Name. Sample data:

Column1| Column2
USA | Portland, Oregon, United States of America
USA | Portland, Maine, United State of America
USA | Bellevue, Washington, United State of America
USA | Bellevue, Nebraska, United State of America

Now user can query using various option. For example:

Portland America 
Portland USA
Portland Oregon USA
Portland Oregon America
Portland United States
... and so on

I am looking to build a query that can solve this for me. I tried the combination of where and orwhere but that didn't solve for it.

To elaborate further, the country name in column one can also be Republic of China. And the query can be random combination of either two words or strings from the same column or column1 and column2 combined. Or it can be just a single word.

aynber · Accepted Answer · 2017-08-21 21:04:30Z

1

In a case like this, you'll want to break up the string and search each part.

DB::table('table_name')->where(function($query) use ($string) {
     $parts = explode(' ', $string);
     foreach($parts as $part) {
          $query->where('Column2', 'LIKE', "%$part%")
     }
})->get();

This will turn the query into something like this:

 SELECT * FROM table_name 
     WHERE Column2 LIKE '%Portland%' 
     AND Column2 LIKE '%America%'

To also check against Column1, add another where clause, but this time use the Or:

DB::table('table_name')->where(function($query) use ($string) {
     $parts = explode(' ', $string);
     foreach($parts as $part) {
          $query->where('Column2', 'LIKE', "%$part%")
     }
})->where(function($query) use ($string) {
     $parts = explode(' ', $string);
     foreach($parts as $part) {
          $query->orWhere('Column1', 'LIKE', "%$part%")
     }
})->get();

This changed the query to be

 SELECT * FROM table_name 
     WHERE (Column2 LIKE '%Portland%' 
        AND Column2 LIKE '%USA%')
     AND (Column1 LIKE '%Portland%' OR Column1 LIKE '%USA%')

This is fine if they search for USA, but not if they search for United States. You may need to refine the search parameters a bit more, or add a way to match abbreviations to country names.

edited Aug 21, 2017 at 21:04

answered Aug 21, 2017 at 20:28

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

techwestcoastsfosea Over a year ago

Thanks. What if lets say there are 100 recurrences of portland in Column 2. And the user searches for Portland USA. Means Portland from Coulum2 and USA from Column 1. Essentially I am trying to figure out a a way to capture the user intent.

aynber Over a year ago

You can add a separate orWhere clause ... I'll edit the answer with that.

techwestcoastsfosea Over a year ago

Is the second where is supposed to be orWhere? Because your mentioned in your comments but the code doesn't show that.

aynber Over a year ago

No, it becomes where (a and b and c) and (d or e or f). From what it sounds like, you want the country to match but it's a single acronym, so that becomes an OR. If you had (a and b and c) or (d or e or f), then it would match USA or Portland (Column2 LIKE '%Portland%' AND Column2 LIKE '%USA%') and (Column1 LIKE '%Portland%' OR Column1 LIKE '%USA%'), which wouldn't narrow down your choices.

aynber Over a year ago

The problem comes in when someone searches for Portland United States, and there's nothing to match Column1

(Column2 LIKE '%Portland%' AND Column2 LIKE '%United%' AND Column2 LIKE '%States%') and (Column1 LIKE '%Portland%' OR Column1 LIKE '%United%' OR Column1 LIKE '%States%')

- no matches.

|

techwestcoastsfosea · Accepted Answer · 2017-08-23 05:34:47Z

0

DB::select( DB::raw(SELECT *, MATCH (column1, coulmn2) AGAINST ('Republic of China Shanghai') AS score FROM table WHERE MATCH (column1, coulmn2) AGAINST ('Republic of China Shanghai'))); provides the answer I was looking for. It essentially uses FULLTEXT SEARCh across both columns.

answered Aug 23, 2017 at 5:34

techwestcoastsfosea

7903 gold badges12 silver badges23 bronze badges

Collectives™ on Stack Overflow

Laravel DB Query with multiple sub strings

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related