Level 51
Dec 6, 2022
11
Level 1
Laravel : eloquent query
Hi. I'm trying to do an eloquent query, where i have a member table, and a booking table. my goal is to get the member info and the total paid on the booking table. So i did this The MemberController :
public function index(){
$members = DB::table('bravo_members')
->join('bravo_bookings', 'bravo_members.user_id', '=', 'bravo_bookings.customer_id')
->orderBy('bravo_members.member_code')
->sum('bravo_bookings.paid');
return view('Member/member_home',['members'=>$members, 'layout'=>'index']);
}
But when i go to the member_home page i got error Invalid argument supplied for foreach()
But when i try to add the get() like this :
public function index(){
$members = DB::table('bravo_members')
->join('bravo_bookings', 'bravo_members.user_id', '=', 'bravo_bookings.customer_id')
->orderBy('bravo_members.member_code')
->sum('bravo_bookings.paid')
->get();
return view('Member/member_home',['members'=>$members, 'layout'=>'index']);
}
I got this error : call to a member function get() on string
How can i resolve it??
The MemberController :
public function index(){
$members = DB::table('bravo_members')
->join('bravo_bookings', 'bravo_members.user_id', '=', 'bravo_bookings.customer_id')
->orderBy('bravo_members.member_code')
->sum('bravo_bookings.paid');
return view('Member/member_home',['members'=>$members, 'layout'=>'index']);
}
The Member Model :
class Member extends Model
{
use HasFactory;
protected $table = 'bravo_members';
protected $fillable = [
'user_id',
'member_code',
'name',
'email'
];
public function user(){
return $this->belongsTo('App\User', 'user_id', 'id');
}
}
The blade file :
<tbody>
@foreach ($members as $mem)
<tr>
<td>{{$mem->name}}</td>
<td>{{$mem->email}}</td>
<td>{{$mem->member_code}}</td>
<td>{{$mem->member_role}}</td>
<td><a href="{{route('memCertif', $mem->user_id)}}" class="card-link text-white">
<button type="button" class="btn btn-primary">View Certif</button>
</a></td>
<td><a href="{{route('memberPfl', $mem->user_id)}}" class="card-link text-white">
<button type="button" class="btn btn-primary">View Profile</button>
</a></td>
<td><a href="{{route('memHistoryCust', $mem->user_id)}}" class="card-link text-white">
<button type="button" class="btn btn-primary">View History</button>
</a></td>
</tr>
@endforeach
</tbody>
Level 102
Is this what you want?
->addSelect(DB::raw('sum(bravo_bookings.paid) as paid_sum')
Level 1
@Sinnbeck I got this error message : Undefined property: Illuminate\Database\MySqlConnection::$name
Did i do this right??
public function index(){
$members = DB::table('bravo_members')
->join('bravo_bookings', 'bravo_members.user_id', '=', 'bravo_bookings.customer_id')
->orderBy('bravo_members.member_code')
->addSelect(DB::raw('sum(bravo_bookings.paid) as paid_sum'));
$member = $members->get();
return view('Member/member_home',['members'=>$members, 'member'=>$member,'layout'=>'index']);
}
Level 102
@Nicho where does that error come from? The shown code does not have "name"
Level 1
@Sinnbeck It's coming from the blade file :
<tbody>
@foreach ($members as $mem)
<tr>
<td>{{$mem->name}}</td>
<td>{{$mem->email}}</td>
<td>{{$mem->member_code}}</td>
<td>{{$mem->member_role}}</td>
<td><a href="{{route('memCertif', $mem->user_id)}}" class="card-link text-white">
<button type="button" class="btn btn-primary">View Certif</button>
</a></td>
<td><a href="{{route('memberPfl', $mem->user_id)}}" class="card-link text-white">
<button type="button" class="btn btn-primary">View Profile</button>
</a></td>
<td><a href="{{route('memHistoryCust', $mem->user_id)}}" class="card-link text-white">
<button type="button" class="btn btn-primary">View History</button>
</a></td>
</tr>
@endforeach
</tbody>
Level 102
@Nicho you are renaming the variable?
->addSelect(DB::raw('sum(bravo_bookings.paid) as paid_sum'))
->get();
Level 1
@Sinnbeck I don't think i rename any variable. I use foreach on the blade
@foreach ($members as $mem)
<tr>
<td>{{$mem->name}}</td>
<td>{{$mem->email}}</td>
<td>{{$mem->member_code}}</td>
<td>{{$mem->member_role}}</td>
@endforeach
And in controller the variable are $members
Level 102
@Nicho Check your code again
$members = DB::table('bravo_members') //MEMBERS WITH AN S
->join('bravo_bookings', 'bravo_members.user_id', '=', 'bravo_bookings.customer_id')
->orderBy('bravo_members.member_code')
->addSelect(DB::raw('sum(bravo_bookings.paid) as paid_sum'));
$member = $members->get(); //MEMBER WITHOUT AN S
Level 1
@Sinnbeck Owhh i see my bad, but still i got an error with different message on the blade
The error is : Undefined property: stdClass::$name
The blade file :
@foreach ($members as $mem)
<tr>
<td>{{$mem->name}}</td>
<td>{{$mem->email}}</td>
<td>{{$mem->member_code}}</td>
<td>{{$mem->member_role}}</td>
@endforeach
Level 102
@Nicho Try this instead
->selectRaw('bravo_members.*, sum(bravo_bookings.paid) as paid_sum'));
Also you are missing a groupBy I would assume
Level 1
@Sinnbeck It work, but strangely it only get id 1, not all the members in the bravo_members table
Please or to participate in this conversation.