Laravel 5.6自定义查询构造器返回空结果求助

Let's work through why your Laravel query is returning an empty collection even though your raw MySQL query pulls the expected result.

First, Verify the auth()->user()->sub Value #

The most likely quick fix here is checking if auth()->user()->sub is actually returning the value you expect. In your working raw query, you used 'ABCDE'—let's confirm the authenticated user's sub matches exactly:

dd(auth()->user()->sub); // Check if this outputs 'ABCDE' with no extra whitespace or case differences

If this doesn't match (e.g., lowercase letters, trailing spaces, or the wrong user is authenticated), your query will return nothing immediately.

Inspect the Exact SQL Laravel Generates #

Laravel lets you log the raw SQL it executes, which is perfect for comparing to your working query. Add this code right before your query:

DB::enableQueryLog();

// Your existing query here
$settings = DB::table('users as u')
    ->join('users_account_link as ual', function ($join) {
        $join->on('u.id', '=', 'ual.user_id')
             ->where('u.account_id_in_use', '=', 'ual.account_id');
    })
    ->join('accounts as a', 'ual.account_id', '=', 'a.id')
    ->select('u.name as user_name', 'u.email as user_email', 'ual.admin as admin_check', 'a.account_name')
    ->where('u.sub', auth()->user()->sub)
    ->get();

// Dump the generated SQL to compare
dd(DB::getQueryLog());

Compare this output to your raw MySQL query. Look for:

• Discrepancies in the WHERE clause (especially the sub value being passed)
• Whether the join conditions are identical to your raw query (make sure u.account_id_in_use = ual.account_id is included in the join logic, not accidentally moved elsewhere)

Test with Hardcoded Value #

To rule out authentication-related issues temporarily, replace auth()->user()->sub with the known working value 'ABCDE':

->where('u.sub', 'ABCDE')

If this returns your expected result, the problem is definitely with the auth()->user()->sub value not matching what's in your database.

Check for Column Type/Collation Mismatches #

A sneaky issue can be column type or collation differences:

• If users.sub is a fixed-length CHAR column, extra whitespace in auth()->user()->sub will break the match
• If your database uses a case-sensitive collation, a mismatch like 'abcde' vs 'ABCDE' will return no results

Align Join Syntax with Raw Query #

You mentioned moving the extra join condition to a WHERE clause didn't help, but try using on() for both join conditions (matching your raw query exactly):

->join('users_account_link as ual', function ($join) {
    $join->on('u.id', '=', 'ual.user_id')
         ->on('u.account_id_in_use', '=', 'ual.account_id'); // Use on() instead of where() here
})

While where() in the join closure should work, using on() mirrors your raw query structure and avoids any unexpected query builder behavior.

内容的提问来源于stack exchange，提问作者Wick 12c