Get a value from two tables

I am trying to get the value of the colum “name” from the clubs table.

First table:(Clubs)

• id*
• name (what i need!!!)
• email
• password

Second table:(club_posts)

• id
• club_id*
• zip_id
• kategory
• type
• stadt
• plz

Controller:

public function search(Request $request)
{
    $zip = $request->input('zip');

        $users = Post::where('zip_id', 'like', $zip)
                        ->orderBy('date')
                        ->paginate(25);

        $clubs = ClubPost::where('zip_id', 'like', $zip)
                        ->orderBy('updated_at')
                        ->paginate(25);

        $city = Zip::where('zip_code', 'like', $zip)
                        ->value('city');

This is my query:

        $names = Club::select('name')
                        ->leftJoin('club_posts', 'club_id', '=', 'clubs.id')
                        ->get();

        dd($names);
  
        return view('search.result', compact('users', 'clubs', 'city', 'names'));
    }

The result is an array with ten values, but need only one.

Answer

Create a relation club in you ClubPost Model so that you can access the club of that club post using the relation.

ClubPost Model

public function club(){
   return $this->belongsTo(AppClub::class, 'club_id', 'id');
}

Now if you want to access the name(or any other property) of the club for a club post, you can use the club relation.

In your blade, you’d be looping over the $club_posts.

@foreach($club_posts as $club_post)

    {{ $club_post->club->name }}
@endforeach

Also, eager load the club relation in your controller.

$club_posts = ClubPost::with('club')
                        ->where('zip_id', 'like', $zip)
                        ->orderBy('updated_at')
                        ->paginate(25);