Laravel - database select query with multiple conditions

Question

I'm trying to create simple search query in db with multiple conditions.

I would like to search in db posts checking tags attached to posts and check searching word in posts title.

So far I made query that checks multiple tags for posts, but I'm struggling now how to check multiple words in post title.

$search = $request['search'];
$searchArr = explode(' ',$request['search']);
$searchTitle = [];

foreach ($searchArr as $search){
    $searchTitle[] = ['title','like', "%$search%"];
}

echo Post::with(['allTags'])->whereHas('allTags', function($query) use ($searchArr) {
    for ($i=0; $i < count($searchArr); $i++) {
        if($i==0) {
            $query->Where('name', $searchArr[$i]);
        }else{
            $query->orWhere('name', $searchArr[$i]);
        }

    }
})->where(['published'=>1])->orWhere([['title','like', "%$search%"],['published','=','1']])->get();

As you see I use the "for" loop to check multiple tags but I can't find a way how to do it in this part of code ->orWhere([['title','like', "%$search%"],['published','=','1']])->get();

Could someone tell me how to do it?

Thank you.

Small tip for the ->whereHas('allTags' closure: You don't need to use $query->where( for the first constraint, you can use $query->orWhere( for all constraints. — Jonas Staudenmeir
– Jonas Staudenmeir, Commented May 4, 2018 at 12:30
I followed your tip, then query pull out from DB all records from table "post". When I use "Where" for first constraint then everything is fine. — Klick
– Klick, Commented May 4, 2018 at 14:35

getjump · Accepted Answer · 2018-05-04 08:06:05Z

1

As far as i understand you need to use whereRaw method.

Be careful this can lead to SQL Injection

foreach ($searchArr as $search) {
    $searchTitle[] = ['title','like', "%$search%"];
}

You should probably build SQL and then bind using underlying PDO object with whereRaw method:

$searchQuery = implode(',', array_map(function($item) { return 'title like \"?\"'; }, $searchArray));

And then in your query string:

...->whereHas('allTags', function($query) use ($searchQuery, $searchArray) {
    $query->whereRaw($searchQuery, $searchArray, 'or');
})

Even easier if you actually are checking against title on Post

...->whereRaw($searchQuery, $searchArray, 'or')

Also make sure to check comments.

answered May 4, 2018 at 8:06

getjump

114 bronze badges

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

1 Comment

Klick Over a year ago

Thank you for answer. If I use this method am I prof from SQL Injections? foreach ($searchWords as $i){ $searchQuery[] = "title like ?"; } "orWhereRaw($searchQuery,$searchWordsArray)->get()" Is it that underlay PDO statements?

Mohamed Akram · Accepted Answer · 2018-05-04 09:38:16Z

-1

as per my understanding you want to break the words in your search string if it contains multiple words as in the following example:

<?php
$search_term = 'test1 test2 test3';
$keywords = explode(" ", preg_replace("/\s+/", " ", $search_term));
foreach($keywords as $keyword){
 $wherelike[] = "title LIKE '%$keyword%' ";
}
$where = implode(" and ", $wherelike);

$query = "select * from table where $where";

echo $query;

and use Laravel whereRaw() method to execute this as a raw query

answered May 4, 2018 at 9:38

Mohamed Akram

2,11716 silver badges22 bronze badges

1 Comment

Klick Over a year ago

Thank you for answer. Why do you use "preg_replace"?

Collectives™ on Stack Overflow

Laravel - database select query with multiple conditions

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related