CODEWITHSUNDEEP
laraveljoineloquentlaravel-5max
tinonetic
tinonetic

Reputation: 8036

Laravel 5.2, Eloquent, join Table 1 to Table 2 and bring MAX column of Table 2

How do I achieve the following equivalent SQL query using Laravel (5.2) and Eloquent...

SELECT 
     products.id
    ,products.name
    ,MAX(bids.bid_price) as maximum_bid
FROM products
    INNER JOIN bids
        ON products.id = bids.product_id 
GROUP BY products.id

Based on the following context:

I have an auction system that has products that users bid on. Meaning one product can have many bids, and one bid can only be for one product.

Now I want to retrieve a product with its current maximum bid

TABLES:

products

bids

MODELS:

Product 

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    /**
     * A Product has many bids
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function bids()
    {
        return $this->hasMany('App\Bid');
    }
}

Bid

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Bid extends Model
{
    /**
     * A Bid belongs to a product
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function product()
    {
        return $this->belongsTo('App\Product','product_id');
    }
}

Upvotes: 0

Views: 298

Answers (2)

tinonetic
tinonetic

Reputation: 8036

This worked using Eloquent.

Product::join('bids', 'products.id', '=', 'bids.product.id')
    ->select(['products.id', 'products.name', DB:raw('MAX(bids.bid_price) as maximum_bid')])
    ->groupBy('products.id')
    ->where('products.id', '=', 123)
    ->get();

Upvotes: 0

user320487
user320487

Reputation:

Try this:

DB::table('products')
    ->select(['products.id', 'products.name', DB:raw('MAX(bids.bid_price) as maximum_bid')])
    ->join('bids', 'products.id', '=', 'bids.product.id')
    ->groupBy('products.id')
    ->get();

Throw a where clause in for a specific product:

DB::table('products')
    ->select(['products.id', 'products.name', DB:raw('MAX(bids.bid_price) as maximum_bid')])
    ->join('bids', 'products.id', '=', 'bids.product.id')
    ->groupBy('products.id')
    ->where('products.id, '=', 123)
    ->get();

Upvotes: 1

Related Questions