Published 3 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?
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:
Notice that the Keyword also needs to be lowercase. So somewhere you need to:
$keyword = strtolower(trim($keyword));
Thanks alot for the explanation, by the way ilike
works perfectly. bless you :)
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.
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
?
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.