Laravel Eloquent with the max in second table?

Question

I've got two tables: listings, which holds the details of a product, and bids, which holds the bid history of the site.

For brief relevance, imagine that listings has the following fields: name, category_id, tagline, short_description, and seller_notes.

In bids we have two relevant fields: listing_id, and bid_amount.

For reasons relevant elsewhere, this needs to be in Eloquent as I need access to the model.

The issue seems to be with the MAX bid amount line or the evaluation of the bid_amount—no matter how I approach it, I always end up with an irrelevant result, but there's no apparent error that I can see.

$listings = \App\Listing::join('bids', 'listings.id', '=', 'bids.listing_id')->select('listings.*','MAX(bids.bid_amount)');

if( !empty($request->keyword) ) {
    $listings = $listings->where('listings.name','LIKE','%'.$request->keyword.'%')
                ->orWhere('listings.tagline','LIKE','%'.$request->keyword.'%')
                ->orWhere('listings.short_description','LIKE','%'.$request->keyword.'%')
                ->orWhere('listings.seller_notes','LIKE','%'.$request->keyword.'%');
}

if( !empty($request->category) ) {
    $listings = $listings->where('listings.category_id','=',$request->category);
}

if( !empty($request->minimum_bid) && !empty($request->maximum_bid) ) {
    $listings = $listings->whereBetween('bid_amount', [$request->minimum_bid, $request->maximum_bid]);
} else {
    if( !empty($request->minimum_bid) ) {
        $listings = $listings->where('bid_amount', '>', $request->minimum_bid);
    }

    if( !empty($request->maximum_bid) ) {
        $listings = $listings->where('bid_amount', '<', $request->maximum_bid);
    }
}

The search is to find results with a current highest bid between minimum_bid and maximum_bid (fields from the search box). The problem presenting is that there may be multiple listings (listing_id) that have a current highest bid (MAX(bid_amount)) between those amounts. I want to display the listings where the MAX(bid_amount) for that bid.listing_id is between minimum_bid and maximum_bid. This could potentially result in multiple listings.

The equivalent MySQL query should be:

SELECT * 
FROM   listings 
       JOIN (SELECT listing_id, 
                    Max(bid_amount) AS bid_amount 
             FROM   bids 
             GROUP  BY listing_id) bids 
         ON bids.listing_id = listings.id 
WHERE  ( listings.NAME LIKE '%keyword%' 
          OR listings.tagline LIKE '%keyword%' 
          OR listings.short_description LIKE' %keyword%' 
          OR listings.seller_notes LIKE '%keyword%' ) 
       AND ( bids.bid_amount > minimum_bid ) 
       AND ( bids.bid_amount < maximum_bid )

I know this is something stupid that I'm doing wrong, I just could really use a fresh set of eyes. Thank you for any help you can provide.

Does listing has bid_id? I am confused by your table structure. You are joining listing table by bids.id. listing.id = bids.id ? — Rutvij Kothari
– Rutvij Kothari, Commented Dec 1, 2017 at 17:41
Now joining makes sense. Coming on to next question. What do you want exactly? Joining based on a maximum bid amount per listing or you want a single record with a maximum bid amount and listing details? — Rutvij Kothari
– Rutvij Kothari, Commented Dec 1, 2017 at 18:08
You want to display MAX(bid_amount) listing from minimum_bid and maximum_bid filter, is that correct? — Rutvij Kothari
– Rutvij Kothari, Commented Dec 1, 2017 at 18:14
I want to display the listings where the MAX(bid_amount) for that bid.listing_id is between minimum_bid and maximum_bid. This could potentially result in multiple listings. — stslavik
– stslavik, Commented Dec 1, 2017 at 18:19
A hint is you need to use Subquery. I tried but no luck. Sorry about that. — Rutvij Kothari
– Rutvij Kothari, Commented Dec 1, 2017 at 18:50

Cy Rossignol · Accepted Answer · 2017-12-08 00:58:02Z

The problem with the code in the question is that it creates a query that tries to find the maximum bid amount over all of the bids when we actually need the max for each group of bids per listing. Let's take a look at how to do this with Eloquent. We'll use local query scopes to encapsulate the logic and clean up the API so that we can call it like this:

$listings = Listing::withMaximumBidAmount()
    ->forOptionalCategory($request->category)
    ->forOptionalBidRange($request->minimum_bid, $request->maximum_bid)
    ->forOptionalKeyword($request->keyword)
    ->paginate($pageSize);

I'm assuming that the Listing model contains a bids() relationship to the Bid model:

class Listing extends Model
{
    ...
    public function bids()
    {
        return $this->hasMany(\App\Bid::class);
    }

Next, let's add the most important scope that maps the maximum bid amount to the model:

public function scopeWithMaximumBidAmount($query)
{
    $bids = $this->bids();
    $bidsTable = $bids->getRelated()->getTable();
    $listingKey = $bids->getForeignKeyName();

    $bidsQuery = \App\Bid::select($listingKey)
        ->selectRaw('MAX(bid_amount) as bid_amount')
        ->groupBy($listingKey);

    $subquery = DB::raw("({$bidsQuery->toSql()}) " . $bidsTable);

    return $query->select($this->getTable() . '.*', 'bid_amount')
        ->join($subquery, function ($join) use ($bids) {
            $join->on(
                $bids->getQualifiedForeignKeyName(),
                '=',
                $bids->getQualifiedParentKeyName()
            );
        });
}

This implementation uses metadata on the models and relations to set the table names and columns for the query so we don't need to update this method if those change. As we can see, we first build a subquery to join on that calculates the highest bid for each listing. Unfortunately, much of this isn't documented—the Laravel source code is a necessary reference for advanced cases.

The example above shows how we can provide a raw SQL subquery for the joined table and pass a closure instead of column names to add any specialized clauses for the join. We create the subquery using the standard Eloquent query builder—so we can safely bind parameters if needed—and convert it to the SQL string using the toSql() method. This scope adds a bid_amount attribute to each Listing returned by the call which contains the highest bid.

Here are the remaining query scopes. These are pretty self-explanatory:

public function scopeForOptionalKeyword($query, $keyword)
{
    if (empty($keyword)) {
        return $query;
    }

    return $query->where(function ($query) use ($keyword) {
        $query->where('name', 'LIKE', "%{$keyword}%")
            ->orWhere('tagline', 'LIKE', "%{$keyword}%")
            ->orWhere('short_description', 'LIKE', "%{$keyword}%")
            ->orWhere('seller_notes', 'LIKE', "%{$keyword}%");
    });
}

public function scopeForOptionalCategory($query, $category)
{
    if (empty($category)) {
        return $query;
    }

    return $query->where('category_id', $category);
}

public function scopeForOptionalBidRange($query, $minimum, $maximum)
{
    if (! empty($minimum)) {
        $query->where('bid_amount', '>=', $minimum);
    }

    if (! empty($maximum)) {
        $query->where('bid_amount', '<=', $maximum);
    }

    return $query;
}

When we have a desired SQL query to match, the toSql() method really helps when constructing a complex query builder object. We may also want to check the indexes on our tables if we experience performance issues. It may be worth indexing bids.bid_amount or tuning keyword lookup columns if we have the resources.

That's just bloody elegant... Thank you. Trying to figure out scopes from the documentation just made me feel like I put my head in a blender. Thank you so much because the setup I had was going to sink our server by month 6.
@stslavik You're welcome! By the way, I updated the last scope. BETWEEN isn't needed--it's equivalent when both less than and greater than are present. Also BETWEEN is inclusive, so I changed the operators to behave similarly.

Sohel0415 · Accepted Answer · 2017-12-05 05:54:21Z

Use DB::raw in your join query to get the Max bid_amount as bid_amount. I have made some changes in your query and it should work now.

$listings = Listing::join(DB::raw('(select listing_id, Max(bid_amount) as bid_amount from bids GROUP BY listing_id) bids'),'bids.listing_id','listings.id');

    if( !empty($request->keyword) ) {
        $listings = $listings->where('listings.name','LIKE','%'.$request->keyword.'%')
            ->orWhere('listings.tagline','LIKE','%'.$request->keyword.'%')
            ->orWhere('listings.short_description','LIKE','%'.$request->keyword.'%')
            ->orWhere('listings.seller_notes','LIKE','%'.$request->keyword.'%');
    }

    if( !empty($request->category) ) {

        $listings = $listings->where('listings.category_id','=',$request->category);

    }

    if( !empty($request->minimum_bid) && !empty($request->maximum_bid) ) {

        $listings = $listings->whereBetween('bid_amount', [$request->minimum_bid, $request->maximum_bid]);

    } else {

        if( !empty($request->minimum_bid) ) {

            $listings = $listings->where('bid_amount', '>', $request->minimum_bid);

        }

        if( !empty($request->maximum_bid) ) {

            $listings = $listings->where('bid_amount', '<', $request->maximum_bid);
        }

    }

    return $listings->get();

Hope it helps :)

stslavik · Accepted Answer · 2017-12-03 21:22:55Z

So I wasn't terribly far off, and this feels pretty hacky... If anyone can advise how to improve, it'd be really helpful. This could easily result in far too many queries, and it's definitely not a best practice by any means. I've just been staring at the damn thing so long that I can't see a better/cleaner way to do it.

    // Create an instance that we can refine.
    $listings = \App\Listing::select('*');

    // Check if the keyword search exists in any relevant column.
    if( !empty($request->keyword) ) {
      $listings = $listings->where('listings.name','LIKE','%'.$request->keyword.'%')
                  ->orWhere('listings.tagline','LIKE','%'.$request->keyword.'%')
                  ->orWhere('listings.short_description','LIKE','%'.$request->keyword.'%')
                  ->orWhere('listings.seller_notes','LIKE','%'.$request->keyword.'%');
    }

    // Check if the result is in the requested category.
    if( !empty($request->category) ) {
      $listings = $listings->where('listings.category_id','=',$request->category);
    }

    $listings = $listings->get();

    // Check for a greatest bid between the min and max.
    if( !empty($request->minimum_bid) || !empty($request->maximum_bid) ) {
      if( !empty($request->minimum_bid) ) {
        $listings = $listings->filter(function($listing) use( $request ){
          return \App\Bid::getCurrentHighestBid($listing->id) >= $request->minimum_bid;
        });
      }

      if( !empty($request->maximum_bid) ) {
        $listings = $listings->filter(function($listing) use( $request ){
          return \App\Bid::getCurrentHighestBid($listing->id) <= $request->maximum_bid;
        });
      }
    }

    // We're left with a collection, but we need to paginate the results.
    // We use the collection to perform a secondary query to get only the
    // relevant rows. *cough*hack*cough*
    $listings = \App\Listing::select('*')->whereIn('id', $listings->pluck('id'))->paginate(30);

    // Pass it on to the view.
    return view('listings.main', ['listings' => $listings]);

I really hope this can help someone, and that it could be improved upon. Laravel has user friendly documentation, but seems to lack a real in-depth reference, so I'm never quite sure if the functions are being used quite the proper way.

robbyrr · Accepted Answer · 2017-12-05 09:55:31Z

0

If i understand you correct, you want to grab all the listings between minimum_bid and maximum_bid, and sort them by listing_id?

You can do this:

//This gets you array of collections sorted by listing_id and sorted from 
  highest bid_amount to lowest.
 $listings = Listing::join('bids', 'listings.id', '=', 'bids.listing_id');

if (!empty($request->minimum_bid)  && !empty($request->maximum_bid)) {

    $listings->whereBetween('bid_amount', [$request->minimum_bid,   
        $request->maximum_bid]);

} else {

    if( !empty($minimum_bid) ) {

        $listings->where('bid_amount', '>', $request->minimum_bid);

    }

    if( !empty($request->maximum_bid) ) {

       $listings=$listings->where('bid_amount', '<', $request->maximum_bid);
    }

}

$listings = $listings->get()
    ->sortByDesc('bid_amount')
    ->groupBy('listing_id');

Result looks like this(i tested with two listings):

    Collection {#196 ▼
  #items: array:2 [▼
    2 => Collection {#184 ▼
      #items: array:3 [▼
        0 => Listing {#204 ▶}
        1 => Listing {#208 ▶}
        2 => Listing {#207 ▶}
      ]
    }
    1 => Collection {#197 ▼
      #items: array:4 [▼
        0 => Listing {#203 ▶}
        1 => Listing {#205 ▶}
        2 => Listing {#206 ▶}
        3 => Listing {#202 ▶}
      ]
    }
  ]
}

edited Dec 5, 2017 at 9:55

answered Dec 4, 2017 at 20:59

robbyrr

3922 silver badges7 bronze badges

1 Comment

stslavik Over a year ago

This was one of the first ways I approached it, but unfortunately since there are many bids for each listing, the return set was all the bids for the listings. Then taking the max to get the highest only returned the one result with the highest bid. Thank you though!

Collectives™ on Stack Overflow

Laravel Eloquent with the max in second table?

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related