Laravel多数据读取及视图展示问题咨询

Hey there! Let's walk through your problem clearly—first, your current controller code isn't working as expected, and I'll explain why, then show you the right ways to fix it and display the data in your view.

Why Your Current Code Is Incorrect #

Your join condition tbl_interest_masters.interest = tbl_interests.id is matching the ID of the tbl_interests row to the interest column in tbl_interest_masters. But what you actually need is to match the comma-separated IDs in the hobby column of tbl_interests to the id column of tbl_interest_masters. That join logic doesn't account for the comma-separated values, so it won't pull the right hobby names.

Fix 1: Using Query Builder (DB Facade) #

Since your hobby column stores comma-separated IDs (like "1,2"), we can use MySQL's FIND_IN_SET() function to match these IDs to the tbl_interest_masters table. Here's how to adjust your controller code:

Example for a Specific User #

Assuming you're fetching hobbies for the currently logged-in user (adjust the where clause to match your actual user identifier):

// First, get the comma-separated hobby IDs from the user's interest record
$userHobbyIds = DB::table('tbl_interests')
    ->where('user_id', auth()->id()) // Replace with your user ID column/value
    ->value('hobby');

// Then fetch the corresponding hobby names from the master table
$interests = DB::table('tbl_interest_masters')
    ->whereRaw("FIND_IN_SET(id, ?)", [$userHobbyIds])
    ->get();

return view('profile')->with('interests', $interests);

One-Line Join Alternative #

If you prefer a single join query instead of two separate ones:

$interests = DB::table('tbl_interest_masters')
    ->join('tbl_interests', function ($join) {
        // Use FIND_IN_SET to check if the master ID exists in the comma-separated hobby string
        $join->on(DB::raw("FIND_IN_SET(tbl_interest_masters.id, tbl_interests.hobby)"), '>', DB::raw('0'));
    })
    ->where('tbl_interests.user_id', auth()->id()) // Filter for your target user
    ->select('tbl_interest_masters.*') // Only get data from the master table
    ->get();

return view('profile')->with('interests', $interests);

Fix 2: Using Eloquent Models (Recommended) #

If you're using Laravel's Eloquent (which is cleaner and more maintainable), first create models for your tables:

• InterestMaster (for tbl_interest_masters)
• Interest (for tbl_interests)

Add a Relationship to the Interest Model #

In app/Models/Interest.php, add a method to fetch the related hobbies:

public function hobbies()
{
    return InterestMaster::whereRaw("FIND_IN_SET(id, ?)", [$this->hobby]);
}

Controller Code with Eloquent #

// Get the user's interest record
$userInterest = \App\Models\Interest::where('user_id', auth()->id())->first();

// Fetch the associated hobby names
$interests = $userInterest->hobbies->get();

return view('profile')->with('interests', $interests);

Displaying Data in Your View #

In your profile.blade.php view, you can display the hobbies in two common ways:

1. Comma-Separated String #

This will output something like "gardening, playing":

<div class="user-hobbies">
    <h3>My Hobbies</h3>
    @if($interests->isNotEmpty())
        <p>{{ $interests->pluck('name')->join(', ') }}</p>
    @else
        <p>No hobbies found.</p>
    @endif
</div>

2. List of Items #

If you want to display each hobby as a list item:

<div class="user-hobbies">
    <h3>My Hobbies</h3>
    @if($interests->isNotEmpty())
        <ul>
            @foreach($interests as $interest)
                <li>{{ $interest->name }}</li>
            @endforeach
        </ul>
    @else
        <p>No hobbies found.</p>
    @endif
</div>

A Quick Note on Database Design #

Storing comma-separated values in a database column is an anti-pattern (not following normalization). Over time, this will make queries slower, harder to maintain, and more error-prone. The better long-term solution is to create a junction table (like user_interests) with user_id and interest_id columns to handle this many-to-many relationship properly. Laravel's Eloquent has built-in support for many-to-many associations, which would make this even simpler!

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