mysql query : get the last row of related table on multi table selection

Question

I have 2 tables structured like this

products

• id
• title

plans

• id
• product_id
• price
• type

Basically the idea is to have multiple prices for each product, the last plan for each product would be its current price and if its deleted or expire it would fall back to the previous plan

So if a product has 2 plans with ids (1, 2) then the plan with id = 2 would be its current price

I want to show products which their last plans has type = off

Here's the SQL Query generated by the Laravel ORM Eloquent

select * from `products` where exists
        (select * from `plans` where `products`.`id` = `plans`.`product_id`
                and `type` = 'off' 
                and `plans`.`deleted_at` is null) 
        and `products`.`deleted_at` is null

The problem is it doesn't check the last/current plan it would search in all the plans... so even if plan with id = 2 type is not off and if plan.id = 1 type is off I'd still ll have this product in the query
here is the php code:

$wonder_product = Product::whereHas('CurrentPlan', function ($q) {
    $q->where('type', 'off');
})->get();

Answer 1

Try with a GROUP BY subquery:

$wonder_product = Product::whereHas('CurrentPlan', function ($q) {
    $q->where('type', 'off')
    ->whereIn('id', function ($subquery) {
        $subquery
        ->from(with(new CurrentPlan)->getTable())
        ->select(DB:raw('MAX(id)'))
        ->groupBy('product_id');
    });
})->get();

Or if you can live with a raw subquery:

$wonder_product = Product::whereHas('CurrentPlan', function ($q) {
    $q->where('type', 'off')
      ->whereRaw('id in (select max(id) from plans group by product_id)')
})->get();

If I'm not wrong, both methods should generate a query like this:

select * from `products`
where exists (
        select * from `plans`
        where `products`.`id` = `plans`.`product_id`
          and `type` = 'off' 
          and `plans`.`deleted_at` is null
          and id in (select max(id) from plans group by product_id)
  ) 
  and `products`.`deleted_at` is null

But if it was me, I would probably write a raw query like this:

$wonder_product = Product::hydrateRaw('
    select products.*
    from products
    where 'off' = (
      select plans.type
      from plans
      where plans.product_id = products.id
        and plans.deleted_at is null
      order by plans.id desc
      limit 1
    )
    and products.deleted_at is null
');