Laravel 5按省份统计数据条目时多字段模糊查询条件异常问题求助

Got it, let's tackle this issue head-on! The root problem here is that your current query is using AND logic for the etitle and edesc checks—meaning only entries where both fields match your $title pattern get counted. You need to switch that to OR logic so entries matching either field are included.

Key Issue in Your Code #

Every time you use:

->where([['t_entry.etitle','ilike',$title], ['t_entry.edesc','ilike',$title]])

Laravel translates this to SQL with AND between the two conditions. That's why your counts are too low—you're filtering out entries that only have a matching title or a matching description.

Modified Solution Code #

Here's the adjusted code with OR logic, plus a small performance tweak (moving the province form ID lookup outside the loop to avoid repeated database calls):

public function getTotalEntryByTitle($title){
    $total = [];
    // Get province list once
    $provinces = t_data_enum::where('ekey', 'province')->orderBy('etext', 'ASC')->get();
    // Get province form ID once (outside loop to save DB queries)
    $provinceFormId = t_entry_form::where([['etype', 1], ['fname', 'field_province']])->first()->fid;

    foreach($provinces as $province){
        $entryQuery = 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')
            // Base filter for active elements
            ->where('t_e_elem.fuse', 1)
            // OR condition for title/description match
            ->where(function($query) use ($title) {
                $query->where('t_entry.etitle', 'ilike', $title)
                      ->orWhere('t_entry.edesc', 'ilike', $title);
            })
            // Filter active entries
            ->where('t_entry.estatus', '1')
            // Filter by province field and value
            ->where('t_e_value.fid', $provinceFormId)
            ->where('t_e_value.vvalue', $province->eval)
            // Ensure unique entries
            ->distinct('t_entry.eid');

        // Get the results and count
        $entryCount = $entryQuery->count(); // Use count() instead of get() + count() for better performance

        $total[] = [
            'name' => $province->etext,
            'count' => $entryCount
        ];
    }
    return $total;
}

What Changed & Why #

1. OR Logic with Closure: The where(function($query) { ... }) wrapper tells Laravel to group those two conditions with OR instead of AND. This ensures entries matching either etitle or edesc are included.
2. Performance Tweak: Moved $provinceFormId outside the loop—no need to run that database query once per province!
3. Simplified Count: Used ->count() directly on the query instead of fetching all results and counting them with count($entrys)—this is more efficient because it lets the database do the counting work.
4. Removed Duplicate Conditions: Your original code had repeated title/desc and fuse filters—we consolidated those into a single, clean query.

Quick Note #

If you're using MySQL instead of PostgreSQL, you'll want to replace ilike with like (since ilike is PostgreSQL's case-insensitive match operator).

内容的提问来源于stack exchange，提问作者ocanrivera