Join Tables not working unless hard code ID-CodePudding

I forgot I have $weekends id already passed so I did this public function

show(Weekend $weekend) 
    {
        $id = $weekend->id; 
        $teams = DB::table('weekends')
        ->join('weekend_team_members', 'weekend_team_members.weekends_id', '=', 'weekends.id')
        ->select('weekend_team_members.firstname','weekend_team_members.lastname','weekend_team_members.position')
        ->where('weekend_team_members.weekends_id', $id)
        ->get();

        return view('pages.weekend')->with(['weekend' => $weekend,'teams'=> $teams]);
    }

This code only works if I change ->where('weekend_team_members.weekends_id', '=','weekends.id') to ->where('weekend_team_members.weekends_id', '=',2) otherwise its an empty array. I am sure its probably something simple but I can't figure it out.

Here is my join statement

 $teams = DB::table('weekends')
    ->join('weekend_team_members', 'weekend_team_members.weekends_id', '=', 'weekends.id')
    ->select('weekend_team_members.firstname','weekend_team_members.lastname','weekend_team_members.position')
    ->where('weekend_team_members.weekends_id', '=','weekends.id')
    ->get();

Weekend Model

   <?php

namespace App\Models\Webmaster;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Weekend extends Model
{
    use HasFactory;
    
    protected $fillable = ['title','verse','songtitle','songvideo','image'];

    public function weekendTeamMembers() {
        return $this->hasMany(App\Models\Webmaster\WeekendTeamMember::class);
    }

 

}

WeekendTeamMember Model

<?php

namespace App\Models\Webmaster;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class WeekendTeamMember extends Model
{
    use HasFactory;
    protected $fillable = ['firstname','lastname','position'];

    public function weekend() {
        return $this->belongsTo(App\Models\Webmaster\Weekend::class);
    }
}

CodePudding user response：

You're looking for the whereColumn() method: https://laravel.com/docs/8.x/queries#additional-where-clauses (scroll down a bit). Right now, you're searching against the literal string weekends.id:

SELECT * FROM `weekends` JOIN ... WHERE `weekend_team_members`.`weekends_id` = 'weekends.id'

The whereColumn() will ensure you're doing the correct SQL:

SELECT * FROM `weekends` JOIN ... WHERE `weekend_team_members`.`weekends_id` = `weekends`.`id`

Notice the difference between usage of single quotes and backticks:

= 'weekends.id'
= `weekends`.`id`

One is a string, one is a DB column, and will produce different results.

So, your final query would be:

$teams = DB::table('weekends')
->join('weekend_team_members', 'weekend_team_members.weekends_id', '=', 'weekends.id')
->select('weekend_team_members.firstname','weekend_team_members.lastname','weekend_team_members.position')
->whereColumn('weekend_team_members.weekends_id', 'weekends.id')
->get();

(Note: the = is implied, it's optional, but ->whereColumn('weekend_team_members.weekends_id', '=', 'weekends.id') is valid too)

CodePudding user response：

First, weekend_team_members.weekends_id isn't going to work natively without defining a column. Laravel expects it to be weekend_id (singular).

Second, your where() clause doesn't accept a column name as an argument and, even if it did, it wouldn't do anything. The "where" you're describing there is already accomplished by your ->join() method (that's what an inner join is).

Third, what is the purpose of the join anyhow? You are only selecting columns from the weekend_team_members table.

If the intention is to filter out weekend_team_members that don't have a weekend_id, then you'd only need to do a ->whereNotNull('weekend_id'). If you wanted to select some columns from the weekends table, you'd be able to do that already from the join you've created.

If you were looking for team members belonging to a specific weekend, then you will need to pass it an ID (otherwise how would the query know which weekend you're looking for?).