How to get the default price of the product with different prices from different date periods from the period closest to today?

Question

Hello there,

according to the above sqlfiddle example;

I have a table A where the products are listed and a table B with different prices for different periods associated with these products.

Here I show these prices according to the date the user has chosen. There is no problem.

However, if the user has not selected a date, I cannot show the price of the period closest to today by default.

In the example I gave, the sql query does this successfully, but I cannot write it successfully in the form of laravel query. Or as an Eloquent orm query

How can I do that?

$query->select(['tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price'])
                            ->join('tableB', function($join) {
                            $join->on('tableA.id', '=', 'tableB.pro_id');
                            })->where(function($sq) use ($postFrom) {
                                $sq->when($postFrom[0]=='0', function ($syq) {
                                    $syq->whereRaw('DAYOFYEAR(curdate()) <= DAYOFYEAR(tableB.end_date)');
                                }, function ($stq) use ($postFrom) {
                                    $stq->whereDate('tableB.start_date', '<=', $postFrom[0])
                                       ->whereDate('tableB.end_date', '>=', $postFrom[0]);
                                });
                            })->orWhere(function($ssq) use ($postTo) {
                                $ssq->whereDate('tableB.start_date', '<=', $postTo[0])
                                    ->whereDate('tableB.end_date', '>=', $postTo[0]);
                    })->groupBy('tableA.id')->orderBy('tableB.price', $sortDirection);

note1: $postFrom and $postTo are the start and end dates from the user. If the user did not submit a date, $postFrom is displayed as 0.

note2: I show the default price when the $postFrom[0] == '0' condition is met.

note3: The '2021-03-07' value in the sqlfiddle example is used for example instead of the dynamic present value.

note4: According to this query, it takes the price value of the first period as default. But that's not what I want.

note5: I can't use 'joinSub' because Laravel version is 5.5.

note6:In the example I want to convert to Laravel Query form, the sql query that works without any problems:

select `tableA`.*, `tableB`.`start_date`, `tableB`.`end_date`, `tableB`.`price`  
from `tableA` 
right join( 
  SELECT id, start_date, end_date, pro_id, price, DATEDIFF(`tableB`.`end_date`, '2021-03-07') diff 
  FROM `tableB` GROUP BY id order by diff asc 
) `tableB` on `tableA`.`id` = `tableB`.`pro_id` where (date(`end_date`) >= '2021-03-07') 
  group by `tableA`.`id` order by `price` desc

Vinie Vinie · Accepted Answer · 2021-03-10T07:17:20

This is an equivalent query of your query. I haven't executed.

If Laravel Version is greater then 5.5

$query1 = DB::table('tableB')
          ->selectRaw("id, start_date, end_date, pro_id, price, DATEDIFF(end_date, '2021-03-07') AS diff")
         ->groupBy('id')->orderBy('diff','ASC');
    
TableA::select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price')
         ->joinSub($query1, 'tableB', function ($join)
        {
              $join->on('tableA.id', '=', 'tableB.pro_id');
        })
        ->whereDate('tableB.end_date','>=','2021-03-07')
        ->groupBy('tableA.id')->orderBy('price','DESC')->get();

For Laravel 5.5

TableA::select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price')
         ->join(DB::raw("(SELECT id, start_date, end_date, pro_id, price, 
         DATEDIFF(`tableB`.`end_date`, '2021-03-07') diff 
         FROM `tableB` GROUP BY id order by diff asc) table2 "), function ($join)
        {
              $join->on('tableA.id', '=', 'table2.pro_id');
        })
        ->whereDate('table2.end_date','>=','2021-03-07')
        ->groupBy('tableA.id')->orderBy('price','DESC')->get();

How to get the default price of the product with different prices from different date periods from the period closest to today?

1 Answers