SAM777's avatar

Help to convert SQL query into Eloquent?

I don't find easy tutorials in order to create complex queries into a DataBase, and as I am facing problems with pagination, using a raw query into DataBase, I prefer to convert it into a Eloquent model, and make pagination a straightforward process.

There's the query I did so far:

$comments = DB::select( DB::raw(" SELECT users.name, users.surname, users.image, reviews.comment, reviews.comment_score FROM reviews JOIN users ON reviews.user_id = users.id WHERE reviews.business_id = :logged_business; ") ,array('logged_business' => $business_id,));

The last 'array' is a way to pass a variable through the query. I don't know doing on Eloquent such comparison of ForeingKeys with PrimaryKeys =( I'm feeling helpless.

Hope you can help me. Thanks a lot ! !

0 likes
4 replies
Snapey's avatar

So you want users that have made reviews for a specific business?

$users = User::whereHas('reviews', function ($query) use($business_id) {
    $query->where('business_id', $business_id);
})-with('reviews')->get();

but ideally you should have a relation that goes the other way;

$reviews = $business->reviews()->with('user')->get();
SAM777's avatar

I am gonna show with a @foreach {{user.name}} and {{reviews.comment}} @endforeach

So I need to send a prepared array with both Tables related, users who made comments of business=X.

Firstly tried witha RAW sentence, but gives me problems with the Pagination, because its a String, not an 'Object'.

I will give to your solution a go! =) Thanks a lot for your help! :')

Snapey's avatar
Snapey
9 years ago
Best Answer
Level 122

if you use my second suggestion

@foreach($reviews as $review)
    reviewer {{ $review->user->name }}
    review: {{ $review->comment}}

@endforeach

Please or to participate in this conversation.