Query Condition in Laravel 5-CodePudding

I want to display the number of data entries in each province based on the query results.

Here is the query I use. But with the code like this, the output I get is only the number of data in each province that contains the conditions ['t_entry.etitle', 'ilike', $title].

Meanwhile, the condition ['t_entry.edesc','ilike',$title] is not executed. So the amount of data I get is small. How to handle it?

    public function getTotalEntryByTitle($title){
    $total = [];

    $province = t_data_enum::where('ekey', 'province')->orderBy('etext', 'ASC')->get();
    foreach($province as $a){
        $entrys = t_e_elem::selectRaw('t_entry.*,t_e_elem.*')
            ->join('t_entry', 't_e_elem.eid', '=', 't_entry.eid')
            ->join('t_e_value', 't_e_elem.fid', '=', 't_e_value.elid')
            ->join('t_entry_form', 't_e_value.fid', '=', 't_entry_form.fid')
            ->where([['t_e_elem.fuse', '=', 1], ['t_entry.etitle', 'ilike', $title], ['t_entry.edesc','ilike',$title]]);
        $entrys = $entrys->where('t_entry.estatus', '1');

        $formIdP = t_entry_form::where([['etype', 1], ['fname', 'field_province']])->first()->fid;
        $entrys = $entrys->where([['t_e_value.fid', '=', $formIdP], ['t_e_value.vvalue', '=', $a->eval],
            ['t_e_elem.fuse', '=', 1]]);

        $entrys = $entrys->distinct("t_entry.eid")
            ->where([['t_entry.etitle','ilike',$title], ['t_entry.edesc','ilike',$title]])
            ->get();

        array_push($total, [
            'name' => $a->etext,
            'count' => count($entrys)
        ]);
    }

    return $total;
}

CodePudding user response：

I will explaine how the query builder build the query in your case:

for this statement as an example:

$entrys = t_e_elem::where([['t_e_elem.fuse', '=', 1], ['t_entry.etitle', 'ilike', $title], ['t_entry.edesc','ilike',$title]]);

this will generate the sql:

select * from `t_e_elems` where (`t_e_elem`.`fuse` = ? and `t_entry`.`etitle` ilike ? and `t_entry`.`edesc` ilike ?)

notice the and, here is the issue, you do not want and usually in the search you need or, so, the sql statement should be:

select * from `users` where `t_e_elem`.`fuse` = ? and (`t_entry`.`etitle` ilike ? or `t_entry`.`edesc` ilike ?)

the Laravel query builder would be:

 t_e_elem::where('t_e_elem.fuse', '=', 1)
            ->where(function ($query) use ($title) {
                $query->where('t_entry.etitle', 'ilike', $title)
                    ->orwhere( 't_entry.edesc','ilike',$title);
            })

the final result would look like:

 $entrys = t_e_elem::selectRaw('t_entry.*,t_e_elem.*')
                ->join('t_entry', 't_e_elem.eid', '=', 't_entry.eid')
                ->join('t_e_value', 't_e_elem.fid', '=', 't_e_value.elid')
                ->join('t_entry_form', 't_e_value.fid', '=', 't_entry_form.fid')
                ->where('t_e_elem.fuse', '=', 1)
                ->where(function ($query) use ($title) {
                    $query->where('t_entry.etitle', 'ilike', $title)->orWhere('t_entry.edesc', 'ilike', $title);
                });
            $entrys = $entrys->where('t_entry.estatus', '1');

            $formIdP = t_entry_form::where([['etype', 1], ['fname', 'field_province']])->first()->fid;
            $entrys = $entrys->where([['t_e_value.fid', '=', $formIdP], ['t_e_value.vvalue', '=', $a->eval],
                ['t_e_elem.fuse', '=', 1]]);

            $entrys = $entrys->distinct("t_entry.eid")
                ->where(function($query) use ($title) {
                $query->where('t_entry.etitle','ilike', $title)->orWhere('t_entry.edesc', 'ilike', $title);})
                ->get();