-> orWhereHas Failed Fetch Data From Multiple Tables

I'm using Laravel 5.2 and PostgreSql and deploy on Heroku

I have a search form to get data from multiple table >> books, writters, publisher and categories.

Here is the controller for the search form

public function search(Request $request)
    {
        $keyword = $request->input('keyword');
        

        // multiple query from different tables
        $query = Book::where('judul','like','%'.$keyword.'%')
        ->orWhere('label','like','%'.$keyword.'%')
        ->orWhere('isbn','like','%'.$keyword.'%')
        ->orWhere('status', 'like', '%'.$keyword.'%')
        ->orWhereHas('writter', function ($query) use ($keyword) {
            $query->where('nama_penulis', 'like', '%'.$keyword.'%');
        })
        ->orWhereHas('category', function ($query) use ($keyword) {
            $query->where('nama_kategori', 'like', '%'.$keyword.'%');
        })
        ->orWhereHas('publisher', function ($query) use ($keyword) {
            $query->where('nama_penerbit', 'like', '%'.$keyword.'%');
        });


        $book_list = $query->paginate(5);
        $pagination = $book_list->appends($request->except('page'));
        $total_book = $book_list->total();
        return view('dashboards.index', compact('book_list', 'keyword', 'pagination', 'total_book', 'nama_penulis'));
    }

the code works like a charm on the local using Mysql.

the problem is after i deploy to the Heroku and using Postgress, the search form only get data from the books table.

I wonder why its ignoring ->orWhereHas. Is it any explanation, or should I get different approach if using postgres?

xmarks

8 years ago

Best Answer

Level 8

I believe the issue might be that Postgres Does not work well with like, as for Postgres like is case sensitive while for MySQL is by default Case Insensitive. Postgres has ilike for case insensitive but that does not work with mySQL...

Try using whereRaw instead. For Example:

$query = Book::whereRaw('LOWER(judul) like ?', "%$keyword%")
    ->orWhereRaw('LOWER(label) like ?', "%$keyword%")
...

This has worked for me on Heroku with no problems.

Also, I advise:

Testing the code 1 by 1, as you add more conditions to see if it fails anywhere else
Install and enable debugbar so you can see the query that is being executed on Heroku-side
Install HeidiSQL which has a PostgreSQL Connection Option. I have found it useful to get the Query executed from DebugBar, and execute it on HeidiSQL to get more information on where the code was failing.

Edit:

Notice that the Keyword also needs to be lowercase. So somewhere you need to:

$keyword = strtolower(trim($keyword));

1 like

yudy

8 years ago

Level 1

Thanks alot for the explanation, by the way ilike works perfectly. bless you :)

1 like

xmarks

8 years ago

Level 8

You're welcome, I am glad that worked out.

As for ilike, I thought it was not recognized on MySQL from what I have read. My solution with whereRaw was meant to add support so your application works both locally on MySQL and Live on PostgreSQL.

yudy

8 years ago

Level 1

I didn't test ilike to my local version with Mysql, but as you mention I think i will be a good idea to have support Mysql and Postgres, so I will try ASAP.

By the way how to use whereRaw with Has, is it whereRawHas?

xmarks

8 years ago

Level 8

You do not need to do that. Instead of:

->orWhereHas('writter', function ($query) use ($keyword) {
            $query->where('nama_penulis', 'like', '%'.$keyword.'%');
        })

You do:

->orWhereHas('writter', function ($query) use ($keyword) {
            $query->whereRaw('LOWER(nama_penulis) like ?', "%$keyword%"); // Only this needs to be changed
        })

1 like

Please or to participate in this conversation.