Eloquent WHERE AND with "sub" OR

Question

I have a question regarding the WHERE clause

in the query I would like to say

SELECT ...
FROM ...
WHERE link.hos_id = $hos_id
AND   outcome.otc_otrdischargedate = $td
AND   outcome.otc_outcome LIKE ['%ICU%', '%I.C.U%', '%L3%']

but instead it reads like this

SELECT ...
FROM ...
WHERE link.hos_id = $hos_id
AND   outcome.otc_otrdischargedate = $td
OR    outcome.otc_outcome LIKE ['%ICU%', '%I.C.U%', '%L3%']

how should the laravel query be structured to read like the first example?

    $matchThese = ['link.hos_id' => $hos_id, 'outcome.otc_otrdischargedate' => $td];
    $icu = DB::table('link')
        ->join('demographic', 'link.lnk_dmgid', '=', 'demographic.dmg_id')
        ->join('admission', 'link.lnk_admid', '=', 'admission.adm_id')
        ->join('outcome', 'link.lnk_otcid', '=', 'outcome.otc_id')
        ->where($matchThese)
        ->orWhere('outcome.otc_outcome', 'like', '%ICU%')
        ->orWhere('outcome.otc_outcome', 'like', '%I.C.U%')
        ->orWhere('outcome.otc_outcome', 'like', '%L3%')
        ->orWhere('outcome.otc_outcome', 'like', '%Level3%')
        ->orWhere('outcome.otc_outcome', 'like', '%Level 3%')
        ->orWhere('outcome.otc_outcome', 'like', '%Intensive Care Unit%')
        ->get();
    $icuSize = sizeof($icu);

SOLUTION -- THANKS @Mehravish Temkar

different query, but same principal

    $array_conditions = array('%ICU%', '%I.C.U%','%L3%','%Level3%','%Level 3%','%Intensive Care Unit%');
    $step_down_icu = DB::table('link')
        ->join('daily_link', 'link.lnk_id', '=', 'daily_link.dlk_lnkid')
        ->join('admission', 'link.lnk_admid', '=', 'admission.adm_id')
        ->join('outcome', 'link.lnk_otcid', '=', 'outcome.otc_id')
        ->where('admission.adm_referraldate', '=', $td)
        ->where('outcome.otc_outcome', 'like', '%ICU%')
        ->Where(function ($query) use($array_conditions) {
            for ($i = 0; $i < count($array_conditions); $i++){
                $query->orwhere('outcome.otc_outcome', 'like',  '%' . $array_conditions[$i] .'%');
            }
        })->get();
    $step_down_icuSize = sizeof($step_down_icu);

for the multiple options - ['%ICU%', '%I.C.U%', '%L3%'], did you try whereIn and pass array instead of writing separate orWhere condtions? — Mehravish Temkar, Sep 22 '16 at 10:57
Replace orWhere to where. Use where for AND and orWhere for OR — Komal, Sep 22 '16 at 11:00

score 6 · Accepted Answer · answered Sep 22 '16 at 11:07

6

This should work:

$array_conditions = array('%ICU%', '%I.C.U%','%L3%'); 

$matchThese = ['link.hos_id' => $hos_id, 'outcome.otc_otrdischargedate' => $td];
    $icu = DB::table('link')
        ->join('demographic', 'link.lnk_dmgid', '=', 'demographic.dmg_id')
        ->join('admission', 'link.lnk_admid', '=', 'admission.adm_id')
        ->join('outcome', 'link.lnk_otcid', '=', 'outcome.otc_id')
        ->where($matchThese)
        ->whereIn('outcome.otc_outcome', 'like', $array_conditions)
        ->get();
    $icuSize = sizeof($icu);

answered Sep 22 '16 at 11:07

Mehravish Temkar

4,275
3
25
44

I like the simplicity of this one, but unfortunately it did not work. – morne Sep 22 '16 at 11:36
@morne it doesn't show the desired records or are there any errors? – Mehravish Temkar Sep 22 '16 at 11:42
@morne http://stackoverflow.com/questions/34329886/laravel-querybuilder-how-to-use-like-in-wherein-function – Mehravish Temkar Sep 22 '16 at 11:46

score 1 · Answer 2 · answered Sep 22 '16 at 10:51

Just switch the orWhere() with where(). The query builder will chain the SQL clauses with AND.

$matchThese = ['link.hos_id' => $hos_id, 'outcome.otc_otrdischargedate' => $td];
$icu = DB::table('link')
    ->join('demographic', 'link.lnk_dmgid', '=', 'demographic.dmg_id')
    ->join('admission', 'link.lnk_admid', '=', 'admission.adm_id')
    ->join('outcome', 'link.lnk_otcid', '=', 'outcome.otc_id')
    ->where($matchThese)
    ->where('outcome.otc_outcome', 'like', '%ICU%')
    ->where('outcome.otc_outcome', 'like', '%I.C.U%')
    ->where('outcome.otc_outcome', 'like', '%L3%')
    ->where('outcome.otc_outcome', 'like', '%Level3%')
    ->where('outcome.otc_outcome', 'like', '%Level 3%')
    ->where('outcome.otc_outcome', 'like', '%Intensive Care Unit%')
    ->get();
$icuSize = sizeof($icu);

um... Obvious_Solutiuon = Face_Palm. Thanks – morne Sep 22 '16 at 11:26 — morne, Sep 22 '16 at 11:26

Eloquent WHERE AND with "sub" OR

2 Answers2