1

I have to write a complex query.

I have mysql table name ads which has 1000 ads.

Right now my pagination showing all ads in descending order by date added column. 20 records per page.

 Select * from ads order by sort_order asc,date_added desc LIMIT 0,20;

New Requirement is

sort_order is assigned base on membership type. So on every page I need to show 20 ads total (5 ads from each membership type). If a membership does not have 5 records on any page next membership will show 5+missing-ads from previous membership.

Now I need order of My page like this

Name    date_added sort_order
ad1      12-2-2015   1     //PREMIUM MEMBER's ADS
ad2      11-2-2015   1     //PREMIUM MEMBER's ADS
ad3      10-2-2015   1     //PREMIUM MEMBER's ADS
ad4      09-2-2015   1     //PREMIUM MEMBER's ADS
ad5      08-2-2015   1     //PREMIUM MEMBER's ADS

ad6      13-2-2015   2     //GOLD MEMBER's ADS
ad7      12-2-2015   2     //GOLD MEMBER's ADS
ad8      07-2-2015   2     //GOLD MEMBER's ADS (There may be chance gold type of mebers has posted only 3 ads)

ad9      18-2-2015    3    //SILVER MEMBER's ADS
ad10      10-2-2015   3    //SILVER MEMBER's ADS
ad11      03-2-2015   3    //SILVER MEMBER's ADS
ad12      02-2-2015   3    //SILVER MEMBER's ADS
ad13      02-2-2015   3    //SILVER MEMBER's ADS
ad14      02-2-2015   3    //SILVER MEMBER's ADS
ad15      01-2-2015   3    //SILVER MEMBER's ADS

ad16      12-2-2015   4       //REGULAR MEMBER's ADS
ad17      08-2-2015   4      //REGULAR MEMBER's ADS
ad18      03-2-2015   4      //REGULAR MEMBER's ADS
ad19      02-2-2015   4      //REGULAR MEMBER's ADS
ad20      01-2-2015   4      //REGULAR MEMBER's ADS

Means if intermediate rows not exist with particular sort_order next order should show more records. As in above example sort_order=2 has 3 records in table so sort_order=3 should show 7. 5+2=7 Also need to keep order by date added in descending for a particular sort order.

Can this be done with single mysql query so my pagination does not stop working. ?

Or any other way.

I have tried union all but that does not work for missing records.

Your help will be appreciated. As I am trying it from last 2 days. Now I decided to ask here.

Asif Asghar
  • 63
  • 4
  • 1
    Can you explain the logic bit more **sort_order=2 has 3 records in table so sort_order=3 should show 5+2=7** – mysqlrockstar Apr 02 '15 at 10:18
  • Yes. It means there may be possibility that only 3 ads exist in table with sort_order=2. So the next sort_order=3 should show 7. As the records per page should remain 20 in order to pagination work correctly. – Asif Asghar Apr 02 '15 at 10:22
  • 1
    Please explain the logic. not something like **should show 7**. From where are you getting 5 and 2 ? – mysqlrockstar Apr 02 '15 at 10:28
  • your question is hard to understand - please show us your sql statement – niyou Apr 02 '15 at 10:30
  • I would suggest that you run four queries, one for each membership type, and do the processing on the application side. There seem like too many edge cases to handle this in the database -- what happens if category 4 has the fewest records? What if you get to a page that only has category 3 records? And so on. – Gordon Linoff Apr 02 '15 at 10:45
  • Following link has solved 70% my problem. http://stackoverflow.com/questions/1091991/how-to-get-the-latest-2-items-per-category-in-one-select-with-mysql – Asif Asghar Apr 02 '15 at 12:02

0 Answers0