本地MySQL正常但Heroku PostgreSQL下Laravel搜索忽略关联表查询问题

I have two versions of my application: one running locally with MySQL, and another deployed on Heroku using PostgreSQL. I'm trying to implement a search form that queries across multiple tables (books, writters, categories, publishers). Here's my Laravel controller code:

public function search(Request $request) {
    $keyword = $request->input('keyword');
    // multiple query from different table
    $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 search works perfectly in my local MySQL environment, but on PostgreSQL (Heroku), it only returns results from the books table—it seems to ignore all the related table queries. Why is this happening?

Answer #

Great question! The most likely culprit here is case sensitivity in PostgreSQL's LIKE operator, which behaves differently from MySQL's default setup. Let me break this down and show you how to fix it:

1. Case Sensitivity Difference Between MySQL and PostgreSQL #

• By default, MySQL uses collation rules (like utf8mb4_general_ci) that are case-insensitive for string comparisons. So a query like WHERE nama_penulis LIKE '%John%' will match john, JOHN, John, etc.
• PostgreSQL's standard LIKE operator is strictly case-sensitive. That means if your writters.nama_penulis field has values like john doe but the user searches for John Doe, PostgreSQL won't find a match—making it look like the related table queries are being ignored.

Fix: Use ILIKE for Case-Insensitive Queries #

PostgreSQL has a built-in ILIKE operator that works exactly like MySQL's case-insensitive LIKE. Update your code to use ilike instead of like across all your search conditions:

public function search(Request $request) {
    $keyword = $request->input('keyword');
    $query = Book::where('judul','ilike','%'.$keyword.'%')
        ->orWhere('label','ilike','%'.$keyword.'%')
        ->orWhere('isbn','ilike','%'.$keyword.'%')
        ->orWhere('status', 'ilike', '%'.$keyword.'%')
        ->orWhereHas('writter', function ($query) use ($keyword) {
            $query->where('nama_penulis', 'ilike', '%'.$keyword.'%');
        })
        ->orWhereHas('category', function ($query) use ($keyword) {
            $query->where('nama_kategori', 'ilike', '%'.$keyword.'%');
        })
        ->orWhereHas('publisher', function ($query) use ($keyword) {
            $query->where('nama_penerbit', 'ilike', '%'.$keyword.'%');
        });
    $book_list = $query->paginate(5);
    $pagination = $book_list->appends($request->except('page'));
    $total_book = $book_list->total();
    // Note: You're passing 'nama_penulis' to the view but it's not defined here—might want to fix that!
    return view('dashboards.index', compact('book_list', 'keyword', 'pagination', 'total_book'));
}

2. Bonus: Check for Table/Field Name Case Issues (Less Likely) #

PostgreSQL automatically converts unquoted table/field names to lowercase, while MySQL's behavior depends on your OS (case-insensitive on Windows, case-sensitive on Linux). If your Laravel models use uppercase names (e.g., protected $table = 'Writters') but your PostgreSQL tables are named writters, this could break the relationship. Double-check your model's table names and PostgreSQL schema to ensure they match.

3. Optional: Clean Up Query Logic for Robustness #

While not the cause of your current issue, wrapping your books table conditions in a nested where clause will make your query more maintainable if you ever need to add additional AND conditions later:

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

After updating to ILIKE, your search should work consistently across both MySQL and PostgreSQL environments!

内容的提问来源于stack exchange，提问作者Yudy Ananda