CODEWITHSUNDEEP
mysqlsqllaraveleloquentquery-builder
mpet
mpet

Reputation: 1014

Get newest instead of oldest item from another table

I have products table with columns ID, Name and reviews table with columns ID, Product_id, Text

I'm trying to get list of product names along with the newest review for every displayed product, but I'm getting the oldest review instead. I'm using the following code:

$products = DB::table('products')
    ->select(array('products.*', 
        DB::raw('COUNT(reviews.product_id) as reviews_count'), 
        DB::raw('reviews.text as review_text')))
    ->where('products.name', 'LIKE', '%'.$search_terms.'%')
    ->join('reviews', 'reviews.product_id', '=', 'products.id')
    ->groupBy('products.id')
    ->orderBy('reviews.id', 'desc')
    ->get();

Upvotes: 0

Views: 63

Answers (1)

Filip Zelic
Filip Zelic

Reputation: 110

Why not use Eloquent for such a simple request? In your model Product create relation to Reviews. One product has many reviews. So model looks like this:

class Product extends \Eloquent {
    protected $fillable = [];

    public function reviews()
    {
        return $this->hasMany('Review');
    }
}

Than in your conntroller you could do something like this:

$product = Product::with('reviews')->firstOrFail();

return View::make('Viewname')->withProduct($product);

And than in your view:

@foreach ($product->reviews->sortBy('created_at') as $review) 
    {{ $review->text }} <br/>
@endforeach

Instead of sortBy, you could you take(3) for example, and you will get last 3 reviews etc.

Upvotes: 3

Related Questions