-> orWhereHas Failed Fetch Data From Multiple Tables

Published 9 months ago by yudy

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?

Best Answer (As Selected By yudy)
xmarks
xmarks
9 months ago

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));
xmarks
xmarks
9 months ago (25,440 XP)

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));
yudy
yudy
9 months ago (120 XP)

Thanks alot for the explanation, by the way ilike works perfectly. bless you :)
xmarks
xmarks
9 months ago (25,440 XP)

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
yudy
9 months ago (120 XP)

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
xmarks
9 months ago (25,440 XP)

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
        })

Please sign in or create an account to participate in this conversation.