Correct way to query 3 tables using Eloquent

I have read the Eloquent relationships document here https://laravel.com/docs/5.2/eloquent-relationships but can't figure out how to accomplish my goal in this project where the system allows users to vote for some people for a title (which is named "danhhieu") agaisnt a list of candidates. So i use 3 tables to record that.

iu_staff (staff_id, staff_name)
iu_danhhieu_type (id, danhhieu_name)
iu_danhhieu_voting (id,danhhieu_id,staff_id,danhhieu_year,user_id,voted_time)

here are models:

class IU_danhhieu_type extends Model
{
    protected $table = 'iu_danhhieu_type';
    
    protected $primarykey = 'id';

    public function staff()
    {
        return $this->belongsToMany('App\IU_staff','iu_danhhieu_voting','danhhieu_id','staff_id');
    }
}
class IU_staff extends Model
{
    protected $table = 'iu_staff';

    protected $primaryKey = 'staff_id';

    public function danhhieu()
    {
        return $this->belongsToMany('App\IU_danhhieu_type','iu_danhhieu_voting','staff_id','danhhieu_id');
    }
}
class IU_danhhieu_voting extends Model
{    
    protected $table = 'iu_danhhieu_voting';    
    protected $PrimaryKey = 'id';
    
}

There is actually another table to contain user infor but I don;t think it's important to list here. Here is the view :

@extends('master')
@section('content')
    <h1>My votes</h1>
    <p>{!! $vote_message !!}</p>

    @if (count($UserVotes) > 0)

        <div class="table-responsive">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>STT</th>
                        <th>Name</th>
                        <th>Year</th>
                        <th>Title</th>
                        <th>Voted at</th>
                    </tr>
                </thead>
                <tbody>

                    <?php $i=1; ?>

                    @foreach($UserVotes as $userVote)
                        <tr>
                            <td>{{$i++}}</td>
                            <td>{{$userVote->staff_id}}</td>
                            <td>{{$userVote->danhhieu_year}}</td>
                            <td>{{$userVote->danhhieu_id}}</td>
                            <td>{{$userVote->voted_time}}</td>
                        </tr>
                    @endforeach
                </tbody>
            </table>
        </div>

    @endif
@endsection

Now I wan to show all votes of a particular user (user # staff) who is currently logged in of a particular year. So here is my Controller:

$user = Auth::user();

$userId = $user->user_id;

$DanhHieuID = 2;

$YearDanhHieu = '2015';

$UserVotes = IU_danhhieu_voting::where(['userid'=>$userId, 'achievement_year'=>$YearDanhHieu])->get();

$vote_message = '';

if (count($UserVotes)> 0)
{
    $vote_message = 'Here are your votes:';
}
else {
    $vote_message = 'You have not voted for anyone. Please <a href="voting">start voting</a>';
}

return view ('my-votes')
    ->with('UserVotes',$UserVotes)
    ->with('vote_message',$vote_message);

This is fine however I want to display staff_name in stread of staff_id, danhhieu_name in stead of danhhieu_id. Can someone tell me how?

Thank you

svcadmin

9 years ago

Best Answer

Level 6

You should be able to grab the name through the relationships, but you do not have one defined in your IU_danhhieu_voting model.

class IU_danhhieu_voting extends Model
{    
        protected $table = 'iu_danhhieu_voting';    
        protected $PrimaryKey = 'id';
    
    public function staff() {
        return $this->belongsTo(IU_Staff::class);
    }
}

Then in your view...

        @foreach($UserVotes as $userVote)
                        <tr>
                            <td>{{$i++}}</td>
                            <td>{{$userVote->staff_id}}</td>
                            <td>{{$userVote->danhhieu_year}}</td>
                            <td>{{$userVote->staff->danhhieu_name}}</td>
                            <td>{{$userVote->voted_time}}</td>
                        </tr>
                    @endforeach

pmall

9 years ago

Level 56

IU_danhhieu_voting belongsTo IU_danhhieu_type
IU_danhhieu_voting belongsTo IU_staff
IU_danhhieu_voting belongsTo User
User hasMany IU_danhhieu_voting

Then :

foreach ($user-> IU_danhhieu_voting as  $IU_danhhieu_voting) {

    $IU_danhhieu_voting->staff->name;
    $IU_danhhieu_voting->type->name;

}

Suxipo

9 years ago

Level 1

@svcadmin,

That did the trick! Thank you

Hi @pmall,

While pvcadmin's method works, your method looks interesting so I want to try it too so that I could learn more about Eloquent. However, I got following error and I don't know how to fix that:

ErrorException in a4bc518a0d4985c4957deb2abf798cd5b83f429a.php line 23: Undefined property: Illuminate\Database\Eloquent\Builder::$voting (View: /var/www/html/tchc-eval/resources/views/my-votes.blade.php)
....

The models:

class user extends Model implements Authenticatable

{   
    public function voting()
    {
        return $this->hasMany('App\IU_danhhieu_voting','user_id','userid');
    }

}

class IU_danhhieu_voting extends Model
{ 
    public function staff(){
        return $this->belongsTo('App\IU_staff','staff_id','staff_id');
    }

    public function danhhieu()
    {
        return $this->belongsTo('App\IU_danhhieu_type','danhhieu_id','id');
    }
    public function user()
    {
        return $this->belongsTo('App\user','userid','user_id');
    }
}

Here is the view:

@foreach ($UserVotes-> voting as  $voting) {
    <tr>
        <td>{{$i++}}</td>
        <td>{{$voting->staff->name}}</td>
        <td>{{$voting->type->name}}</td>
    </tr>
}
@endforeach

I keep my controller intact so it still looks like in the first post.

pmall

9 years ago

Level 56

You just probably messed up with the keys / made a typo

Please or to participate in this conversation.