How to use where condition in joins when using more than two table

I've four tables attendees partners speakers events. Here events table is the parent table. I mean other three tables are having foreign key event_id from events. I want to fetch first_name from child tables. I'm using joins here. My controller code is show below

public function show($id)
     {
    $disp = DB::table('events')
    ->join('attendees','events.id','attendees.event_id')
    ->join('partners','events.id','partners.event_id')
    ->join('speakers','events.id','speakers.event_id')
    ->select('attendees.first_name as atts','partners.first_name as partName','speakers.first_name as speakName')->get();
    return view('ConferenceDetails',['disp'=>$disp,'id'=>$id]);
    }

i'm passing id from blade say id is 4 and below is code from blade

<div class="col-sm-10"> 
  @foreach($disp as $check)
  <p>{{$check->atts}}</p>
  <p>{{$check->partName}}</p>
  <p>{{$check->speakName}}</p>
  @endforeach
</div>

here my problem is I'm first_name from all rows. I want to fetch first_name from only those rows, whose event_id is 4. how can I use where clause here? My current output is as show below

john
andrew
keety
john
smith
keety

partners table has two rows so the foreach loop using twice.

Snapey

8 years ago

Level 122

use eloquent and relationships. It makes it so much easier.

$event = Event::with('attendees','partners','speakers')->firstOrFail($id);

and then in the view

    <p>{{$event->attendee->first_name }}</p>
    <p>{{$event->partner->first_name }}</p>
    <p>{{$event->speaker->first_name }}</p>

in your DB case, you need to add a where clause to the query, and specify the event id that you are interested in.

// edit sorry, my example is not quite right because I assume there can be multiple attendees and multiple partners and I don't know how you will relate those together

Abhilash19

8 years ago

Level 1

@Snapey tables will be having multiple rows. Right now I've one row in attendee and speaker tables. And partners table is having two rows

abhigarg

8 years ago

Level 1

@Abhilash19 have you considered using Eloquent to do this for you?

Your Events model would have:

public function attendees()
    {
        return $this->hasMany('App\Models\Attendees', 'event_id');
    }

public function partners()
    {
        return $this->hasMany('App\Models\Partners', 'event_id');
    }

You can then just fire:


{{ $eventsModel->partners->first_name }}

in your foreach loop in the blade.

You can also have many to many:

public function partners()
    {
        return $this->belongsToMany(
            'App\Models\PartnerType,
            'partner_event',
            'event_id',
            'partner_id')
            ->withPivot('id')
            ->withTimestamps();
    }

hasMany() is a pivot table-driven setup.

Read more here: https://laravel.com/docs/5.5/eloquent-relationships#many-to-many-polymorphic-relations

the solution is similar to what Snapey suggested with the exception that you can permanently set up these relationship bindings in your models for all queries.

Snapey

8 years ago

Level 122

@abhigarg

Same problem;


 public function partners()
    {
        return $this->hasMany('App\Models\Partners', 'event_id');
    }

is has many

So you cannot do;

{{ $eventsModel->partners->first_name }}

because partners will be a collection (even if there is only one result)

Please or to participate in this conversation.