欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

Laravel 5 query `LIKE` issue where like 可以用原生 raw 有大用

星期四, 2017-07-20 10:41 — adminshiping1

0down votefavorite
 

I have a special_number table with a prefix column. The column contains 022,021 etc data. I have a number 0216627363021. How I match the column using LIKE keyword to get the row.

$rate = Special::where('user_id',$user_id)
            ->where(DB::raw("prefix LIKE $number"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-1)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-2)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-3)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-4)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-5)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-6)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-7)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-8)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-9)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-10)"))
            ->first();

but it return NULL. Where is the problem? Thank you.


0down votefavorite
 

I have a special_number table with a prefix column. The column contains 022,021 etc data. I have a number 0216627363021. How I match the column using LIKE keyword to get the row.

$rate = Special::where('user_id',$user_id)
            ->where(DB::raw("prefix LIKE $number"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-1)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-2)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-3)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-4)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-5)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-6)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-7)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-8)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-9)"))
            ->orWhere(DB::raw("prefix LIKE SUBSTR($number,1,-10)"))
            ->first();

but it return NULL. Where is the problem? Thank you.

up vote0down vote

If you want to match a part of the value just use wildcard with like:

$rate = Special::where('user_id', $user_id)
        ->where('prefix', 'like', '%' . $number . '%')
        ->first();

This will match the string inside the prefix at any point. If you want to get results for the prefix value that starts with the number use:

->where('prefix', 'like', $number . '%')

Or if you want to match prefix values that end with that number use:

->where('prefix', 'like', '%' . $number)




来自 https://stackoverflow.com/questions/32285747/laravel-5-query-like-issue

substring inside query

yongpin92 posted 2 years ago
Eloquent

how to do substring inside query if the name are kevin_001

User::where(substr('name', 0, strpos('name', '_'), '=', 'kelvin');

 
Xum replied 2 years ago

@yongpin92,

you can use DB::raw method for this:

User::where(DB::raw("substr('name', 1, strpos('name', '_'))"), '=', 'kelvin');
来自  https://laravel.io/forum/06-30-2015-substring-inside-query]


up vote1down votefavorite
 

If i try WHERE email = ? it works, but if i try the code bellow it doesnt. Any ideas? Thanks.

DB::connection('operator')->select("SELECT * FROM users WHERE email LIKE '%?%'", array('test'));

This is how the getQueryLog() looks like.

  array(3) {
    ["query"]=>
    string(213) "SELECT * FROM users WHERE email LIKE '%?%'"
    ["bindings"]=>
    array(1) {
      [0]=>
      string(1) "test"
    }
    ["time"]=>
    float(1.45)
  }
   
shareimprove this question
 
1 
Problem is if it is using PDO for binding then you get into problems because PDO automatically encloses the parameters in single quotes resulting in a query looking like SELECT * FROM users WHERE email LIKE '%'test'%' when it executes the query on the server. – ITroubs Jan 9 '15 at 15:36
1 
Why don't you use the Laravel ORM or QueryBuilder? – ITroubs Jan 9 '15 at 15:36
   
@ITroubs: that's not pdo's fault. placeholders cannot be quoted in the first place. foo='?' is a string literal question mark, foo=? is a placeholder. – Marc B Jan 9 '15 at 15:38
   
@MarcB hmm you are right. My mistake. – ITroubs Jan 9 '15 at 15:39
up vote11down vote

You have to put the % in the bindings array:

DB::connection('operator')
    ->select("SELECT * FROM users WHERE email LIKE ?", array('%test%'));

Also it would be a lot easier to just use Laravel's Eloquent ORM or Query Builder

For example that's what it would look like with the query builder:

DB::connection('operator')->table('users')->where('email', 'LIKE', '%test%')->get();

Remember to escape any % when using a string provided by the user! Like this:

$escapedInput = str_replace('%', '\\%', $input);
shareimprove this answer
 
1 
Right answer but i would still rather see you giving the OP the recomendation to use the query builder or a eloquent model with a scope – ITroubs Jan 9 '15 at 15:38
   
@ITroubs There you go :) – lukasgeiter Jan 9 '15 at 15:39
   
Right that's better ;-) – ITroubs Jan 9 '15 at 15:39
2 
You have to remember to escape any % in the user-provided string. – Joseph Silber Jan 9 '15 at 15:47
1 
@lukasgeiter - str_replace('%', '\\%', $email) – Joseph Silber Jan 9 '15 at 16:22
up vote0down vote

Just escaping your variable works:

$name = "_";
Model::where("field", "LIKE", "\\" . $name . "%")->get()

Hope this helps someone.

shareimprove this answer
 
   
Can you add more information on why? I can't seem to locate the double backslash functionality. Is that laravel specific? – Blizz Jun 1 at 6:03
   
I don't realy have a clear explanation, but that's what works for me – Herman Demsong Jun 1 at 13:20

来自  https://stackoverflow.com/questions/27863956/laravel-how-do-you-select-where-like


 
up vote0down votefavorite
 

I use Laravel 5.3

My laravel eloquent is like this :

$query = User::where('year', '=', (string)$object->year)               
             ->where(\DB::raw('substr(code, 1, 3)'), '=', 511)
             ->get();

I try like that, but it does not work

How can I solve it?

   
shareimprove this question
 
   
Are you trying to use substr of php or the database? Also, which database are you working with? – linuxartisan May 4 at 3:08
   
@linuxartisan, I use mongodb and mysql – samuel toh May 4 at 4:04
1 
Tried where(\DB::raw('substr(code, 1, 3)'), '=', '511') ? – linktoahref May 4 at 4:44
   
Well, I know substr is a php function and is not a mysql function. So please tell whether you are trying to use the php substr or not. If yes, then the way you are trying it is incorrect. You can't give it to mysql to interpret. – linuxartisan May 4 at 8:45
up vote0down vote

you forgot to put comma after '=' sign. try this .

   $query = User::where('year', '=', (string)$object->year)               
           ->where(\DB::raw('substr(code, 1, 3)'), '=' , 511)
           ->get();
shareimprove this answer
 
   
It does not work – samuel toh May 4 at 4:29
   
i think there is a problem in $object->year that you are passing in where condition . – PHP Dev May 4 at 5:30

来自 https://stackoverflow.com/questions/43772915/how-can-i-use-substr-in-laravel-eloquent


 

普通分类: 
laravel
Powered by Drupal

友情链接

校园好文网

 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2