CODEWITHSUNDEEP
mysqllaraveloctobercms
Ersin
Ersin

Reputation: 39

Retrieving data from the row containing the closest date to today with whereDate

I want to sort by price after filtering and grouping by date. However, because there are more than one relation, I cannot get the result I want.

The result I want is to get the price of the relation that is the closest to the end_date and sort it accordingly.

For this, the query, sql output, tables and demo page are as follows. Thanks in advance ..

demo sqlfiddle

$query->join('tableB', 'tableA.id', '=', 'tableB.pro_id')
        ->select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.old_daily')
        ->where(function($sq) {
            $today = Carbon::now()->format('Y-m-d'); 
            $sq->whereDate('end_date', '>=', $today);
            })
        ->groupBy('tableA.id')
        ->orderBy('price', desc);

Query:

select `tableA`.*, `tableB`.`start_date`, `tableB`.`end_date`, `tableB`.`price` 
from `tableA` 
    inner join `tableB` on `tableA`.`id` = `tableB`.`pro_id` 
where (date(`end_date`) >= 2021-03-07) 
group by `tableA`.`id` 
order by `price` desc

tableA

| id | title |
|----|-------|
|  1 |  pro1 |
|  2 |  pro2 |
|  3 |  pro3 |

tableB

| id | start_date |   end_date | price | pro_id |
|----|------------|------------|-------|--------|
|  1 | 2021-06-01 | 2021-06-05 |   750 |      2 |
|  2 | 2021-05-01 | 2021-05-05 |   850 |      2 |
|  3 | 2021-04-01 | 2021-04-05 |   650 |      2 |
|  4 | 2021-06-01 | 2021-06-05 |  2750 |      1 |
|  5 | 2021-05-01 | 2021-05-05 |  2850 |      1 |
|  6 | 2021-04-01 | 2021-04-05 |  2650 |      1 |
|  7 | 2021-06-01 | 2021-06-05 |  1750 |      3 |
|  8 | 2021-05-01 | 2021-05-05 |  1850 |      3 |
|  9 | 2021-04-01 | 2021-04-05 |  1650 |      3 |

Upvotes: 0

Views: 93

Answers (2)

Ahmet Çetintaş
Ahmet Çetintaş

Reputation: 156

this query gives the result you want. It would be a good choice to use "right join" in this step.

sqlfiddle

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

Upvotes: 1

Anurat Chapanond
Anurat Chapanond

Reputation: 2987

the closest to the end_date and sort it accordingly.

you should find the difference between the given date and end date then sort ascendingly.

ORDER BY DATEDIFF(end_date, '2021-03-07') ASC

Upvotes: 0

Related Questions