CODEWITHSUNDEEP
phpmysqllaravel
Rp9
Rp9

Reputation: 1963

Mysql get data by year with pagination

I need to get the data based on year with pagination,if the rows count is less,then search in next year

SELECT * 
FROM `user_notifications` 
WHERE DATE_FORMAT(created_at, '%Y') = '2019' 
ORDER BY `created_at` DESC
LIMIT 0,10

if rows count is less than 10,then search by year 2018

Upvotes: 2

Views: 107

Answers (4)

Nick
Nick

Reputation: 147156

Given that your results are sorted by created_at DESC, I don't think a WHERE clause is necessary at all. If there are insufficient results from 2019, your query will automatically return results from 2018, 2017, 2016 etc. as necessary to get to 10 rows:

SELECT * 
FROM `user_notifications` 
ORDER BY `created_at` DESC
LIMIT 10

Upvotes: 1

Maxim
Maxim

Reputation: 2391

Use https://dev.mysql.com/doc/refman/8.0/en/year.html

SELECT *
FROM user_notifications
WHERE YEAR(created_at) <= $searchYear
ORDER BY created_at DESC
LIMIT 0, 10;

Upvotes: 0

Arth
Arth

Reputation: 13110

If I've read it right, this sounds like just a simple ordering exercise

  SELECT *
    FROM user_notifications
   WHERE created_at < :input_year + INTERVAL 1 YEAR
ORDER BY created_at DESC
   LIMIT :offset, 10;

If you only want to go back one year you can just add another condition

  SELECT *
    FROM user_notifications
   WHERE created_at < :input_year + INTERVAL 1 YEAR
     AND created_at >= :input_year - INTERVAL 1 YEAR
ORDER BY created_at DESC
   LIMIT :offset, 10;

Not that using a function like YEAR() on created_at rather than a comparison (<,>=) will prevent the engine from using an index on created_at

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

I think you just need a WHERE clause, to include both 2018 and 2019:

SELECT *
FROM user_notifications
WHERE YEAR(created_at) IN (2018, 2019)
ORDER BY created_at DESC
LIMIT 10;

Upvotes: 0

Related Questions