Laravel Select2搜索

Question

I use the select2 jquery plugin but I can't search for the data that I want to output. I've been trying to search the name of a book in my database which should be available and with the company branch, I am currently in. I'm dealing with 2 tables for this.

books

book_type_id [1, 2]

status ['Available', 'Available']

book_type

id [1, 2] name ['Book 1', 'Book 2']

and

public function get_available_book_type(Request $request){
    $employee = employee::where('id', Auth::user()->emp_id)->first();
    $bt = books::with('book_type')->where('branch_id', $employee->branch_id)
                    ->where('status', 'Available')->where('id', 'LIKE', '%'.$request->name.'%')->groupBy('book_type_id')->get();

    $book_type = $bt->where('book_type.name', 'like', '%'.$request->name.'%');

    $array = [];
    foreach ($book_type as $key => $value){
        $array[] = [
            'id' => $value['id'],
            'text' => $value['book_type']['name']
        ];
    }
    return json_encode(['results' => $array]);
}

This results in a "no results found". However, when I use

$book_type = $bt->where('book_type.name', $request->name);

It returns a data so I believe my initial query is correct and not empty but this is not what I want because I use this for searching and I don't expect my user to type the whole word to output it to select2.

The $book_type like query works if I don't use relationship.

Answer 1

You can use whereHas method to filter through the relations:

$book_type = books::with('book_type')
             ->where('branch_id', $employee->branch_id)
             ->where('status', 'Available')
             ->where('id', 'LIKE', '%'.$request->name.'%')
             ->whereHas('book_type', function($query) use ($request) {
                 $query->where('book_type.name', 'LIKE', '%'.$request->name.'%');
             }
             ->groupBy('book_type_id')->get();