CODEWITHSUNDEEP
phpmysqllaravelleft-join
SCloud
SCloud

Reputation: 1

Sql query not populate correct records in laravel

I am trying to filter data with search terms. But its not populate data. Any one can help me with this? Here is my code:

$query = Lesson::select('lesson.*')
                ->join('lesson_language', 'lesson_language.lesson_id', 'lesson.id')
                ->leftJoin('content', 'content.lesson_id', 'lesson.id')
                ->leftJoin('lesson_role', 'lesson_role.lesson_id', 'lesson.id')
                ->leftJoin('lesson_brand', 'lesson_brand.lesson_id', 'lesson.id')
                ->leftJoin('lesson_category', 'lesson_category.lesson_id', 'lesson.id');
            

            // filter by search term
            if(isset($request['search_term']) && $request['search_term'] != '') {
                $term = strtolower($request['search_term']);
                $term = str_replace('"', '', str_replace("'", '', $term));

                if((clone $query)->where('lesson_language.name', 'LIKE', "{$term}%")->count()) {
                    $query->where('lesson_language.name', 'LIKE', "{$term}%");
                }
                else {

                    $searchValues = preg_split('/\s+/', $term, -1, PREG_SPLIT_NO_EMPTY);
                    $query->where(function ($q) use ($term) {
                            $q->orWhere('lesson_language.name', 'LIKE', "%{$term}%")
                                ->orWhere('lesson.content_skus', 'LIKE', "%{$term}%");
//                                ->orWhere('lesson.content_terms', 'LIKE', "%{$subTerm}%")
//                                ->orWhere('lesson.id', $subTerm);                        
                    });
                    

                }
            }

This else statement not work. Here is the sql query for that.

`` select lesson.* from lesson inner join lesson_language on lesson_language.lesson_id = lesson.id left join content on content.lesson_id = lesson.id left join lesson_role on lesson_role.lesson_id = lesson.id left join lesson_brand on lesson_brand.lesson_id = lesson.id left join lesson_category on lesson_category.lesson_id = lesson.id where (lesson_language.name LIKE '%washer and dryer maintenance%') and lesson.deleted_at is null

``

Could anyone can help me?

I am trying to search "washer and dryer maintenance" and this record is exists in database. But Its not fetch.

Upvotes: -1

Views: 44

Answers (2)

I Made Purnama Yasa
I Made Purnama Yasa

Reputation: 11

$obat = new Obat;

if (strlen($cari_nama) > 0) {
    $obat = $obat->where('nama_dagang', 'like', "%$cari_nama%")
    ->orWhere('nama_obat', 'like', "%$cari_nama%");
}

if (strlen($cari_kategori) > 0) {
    $obat = $obat->where(DB::raw('TRIM(catagory)'), $cari_kategori);
}

if (strlen($cari_satuan) > 0) {
    $obat = $obat->where(DB::raw('TRIM(satuan)'), $cari_satuan);
}        

$obat = $obat->orderBy('nama_dagang')
    ->paginate(10)
    ->appends([
        'cari_nama' => $cari_nama,
        'cari_satuan' => $cari_satuan,
        'cari_kategori' => $cari_kategori,
    ]);

this is my example if you want to filter data in your query

Upvotes: 0

I Made Purnama Yasa
I Made Purnama Yasa

Reputation: 11

have you called the function

->get();

at the end

Upvotes: 1

Related Questions