Multiple like in a query with OR

Question

I need to add in this cakephp 3 another "like", the query has to check also in the last_name field. How can i do it, i want to respect the CakePHP 3 rules. thanks.

 $users = $this->Users
        ->find()
        ->where(function ($q) use ($keyword) {
            return $q
            ->like('Users.first_name', "%$keyword%");
            // OR ->like('Users.last_name', "%$keyword%");
        })
        ->limit(5)
        ->toArray();

score 4 · Accepted Answer · answered Jan 04 '16 at 11:45

this should work if you really want to use cakephp expressions

->where(function ($q) use ($keyword) {
    return $q
        ->or_($q->like('Users.first_name', "%$keyword%"))
        ->like('Users.last_name', "%$keyword%");

    })

but you can simply do

->where([
    'OR' => [
        'Users.first_name LIKE' => "%$keyword%",
        'Users.last_name LIKE' => "%$keyword%"
    ]
])

score 0 · Answer 2 · answered Jan 04 '16 at 11:35

0

It's not the best way, but this might work:

 $users = $this->Users
        ->find()
        ->where(function ($q) use ($keyword) {
            return $q
            ->like('Users.first_name', "%$keyword% OR Users.last_name LIKE %$keyword%");
        })
        ->limit(5)
        ->toArray();

If that doesn't work, this section of the CakePHP Documentation might help

answered Jan 04 '16 at 11:35

Axiom

902
1
10
23

Hi, thanks for your help, just tried and the query doesn't work. – Brian Millot Jan 04 '16 at 11:38
A normal (raw) sql query for what you're trying to do would normally be something like `SELECT * FROM Users WHERE Users.first_name LIKE '%$keyword%' OR Users.last_name LIKE '%$keyword%' LIMIT 5`, however using `CakePHP`, I'm not too sure exactly how you'd go about doing that. In the CakePHP Documentation linked above, however, it shows you can make raw SQL queries, so I recommend to look into that if possible. It just depends on how you have everything set up, really. – Axiom Jan 04 '16 at 11:42
2

@Axiom You'd only do _that_ if you'd wanted to implement an SQL injection vulnerability :) – ndm Jan 04 '16 at 11:51
@ndm well, I figure since it's being passed in as %$keyword$ anyway, CakePHP itself automatically sanitizes that argument in the `like()` function. Otherwise, yeah you'd want to sanitize the `$keyword` variable before passing it into the `like()` function. – Axiom Jan 04 '16 at 11:55
I was just referring to your raw query example ;) – ndm Jan 04 '16 at 11:57
It wouldn't necessarily as long as they ran something such as `htmlentities($string, ENT_QUOTES, 'UTF-8');` and used pdo/prepared statements. But, I see what you mean. I should've mentioned it instead of assuming he knew to sanitize lol – Axiom Jan 04 '16 at 12:00

Multiple like in a query with OR

2 Answers2