4

The following request extract all entries from the users table where name=Albert or name=Alberto or name=Ana.

$users = DB::table('users')
    ->whereIn('name', ['Albert', 'Alberto', 'Ana'])
    ->get();

Is there is possibility to adapt this request to extract all entries that begin with the following names ?

wawanopoulos
  • 9,614
  • 31
  • 111
  • 166

4 Answers4

6

Use the like operator on the basic where function:

$users = DB::table('users')
            ->where('name', 'like', 'T%')
            ->get();

Source

Edit: Based on this answer (but simplified), here is a simple closure that solves your question:

$queryStr = ['Albert', 'Alberto', 'Ana'];
$users = DB::Table('users')            
    ->where(function ($query) use ($queryStr) {
         foreach($queryStr as $q){
            $query->orwhere('name', 'like',  $q . '%');
         }      
    })->get();
QuickDanger
  • 1,032
  • 11
  • 18
  • Yes but in your example there is only one string to check. I would like to make the request with the provided array of string – wawanopoulos Dec 19 '17 at 16:33
  • That's not possible. How is the same string supposed to start with both "Albert" and "Ana"? No string matches that. Maybe you mean `->orWhere()`. – DevK Dec 19 '17 at 16:51
3

Why not chaining orwhere clause:

$names = ['Albert', 'Alberto', 'Ana'];

$users = DB::table('users');

foreach ($names as $name) {
    $users->orWhere('name', 'like', $name.'%');
}

$users = $users->get();
YouneL
  • 8,152
  • 2
  • 28
  • 50
0

->whereRaw(name in(\'Albert\', \'Alberto\', \'Ana'\)')

or 

->where(name, 'LIKE', 'Albert%')
->orWhere(name, 'LIKE', 'Ana%')
derrickrozay
  • 1,048
  • 3
  • 15
  • 37
  • 1
    Please augment your code-only answer with some explanation, in order to reduce the impression that StackOverflow is a free code writing service. – Yunnosch Dec 19 '17 at 19:04
0

Simplest method

The simplest method is nesting the where and orWhere methods of the QueryBuilder:

<?php

$users = DB::table('users')
            ->where('name', 'like', 'Albert%')
            ->orWhere('name', 'like', 'Alberto%')
            ->orWhere('name', 'like', 'Ana%')
            ->get();    

?>

More general applicable for large array of names

You can use the whereRaw method of the QueryBuilder, and combine this with some basic php and sql. In this way MySQL injection is also automatically prevented.

<?php

$names = [
    'Abert',
    'Alberto',
    'Ana'
];

$conditions = [];
$sqlnames = [];

// of course you can append the wildcards directly, 
// but this is more general applicable
foreach($names as $name){
    $sqlnames[] = $name . '%';
    $conditions[] = '`name` LIKE ?';
}


// run the query
$users = DB::table('users')
            ->whereRaw(implode(' OR ', $conditions), $sqlnames)
            ->get();

?>
Jort Jacobs
  • 33
  • 4
  • In terms of performance, what is the best method ? Use whereRaw or use where closure ? – wawanopoulos Dec 19 '17 at 17:29
  • In general raw SQL is always faster. And if you refer to my example: it is fastest if you hard code the SQL query instead of generating it from the $names array. – Jort Jacobs Dec 19 '17 at 19:05