How to propagate records based on a column
I have a table that stores competition records. Participants must enter text and, if necessary, upload a photo
When I display posts on a page, I am paginating 9 at a time.
How can I ensure as much as possible so that each page contains at least one post with a photo in it (assuming there are enough photos to post on one page)? It would probably be enough to distribute posts with photos evenly among the pages.
source to share
This was one of the hardest questions I've seen recently - thanks for that! I can't get it to work with a single SQL statement, but I was able to get it to work (at least it appears) like this. It basically tries to determine how many results will be returned, then how many of them have photos, and uses the percentage of photos divided by the number of pages (using CEILING to provide at least one for the first few pages).
Anyway, here goes:
SET @page = 1;
SET @resultsPerPage = 9;
SELECT @recCount:= COUNT(Id) as RecCount
FROM Entries;
SELECT @photoCount:= COUNT(Photo) as PhotoCount
FROM Entries
WHERE Photo IS NOT NULL;
SET @pageCount = CEILING(@recCount/@resultsPerPage);
SET @photosPerPage = CEILING(@photoCount/@pageCount);
SET @nonPhotosPerPage = @resultsPerPage - CEILING(@photosPerPage);
SELECT *
FROM (
SELECT *,
@rownum := @rownum + 1 row_number
FROM Entries JOIN (SELECT @rownum := 0) r
WHERE Photo IS NOT NULL
) a
WHERE a.row_number > (@photosPerPage*(@page-1))
and a.row_number <= (@photosPerPage*(@page))
UNION
SELECT *
FROM (
SELECT *,
@rownum2 := @rownum2 + 1 row_number
FROM Entries JOIN (SELECT @rownum2 := 0) r
WHERE Photo IS NULL
) b
WHERE b.row_number > (@nonPhotosPerPage*(@page-1))
and b.row_number <= (@nonPhotosPerPage*(@page))
And SQL Fiddle .
Good luck!
source to share
I would assume that you are ordering strings arbitrarily:
order by rand()
It doesn't guarantee a photo on every page, but it does help.
An alternative is to do something like this:
select *, @seqnum:=@seqnum+1
from t
where nophoto
select *, @seqnum:=@seqnum+8
from t
where photo
Then sort by seqnum. What makes it cumbersome is handling cases where there is at most one photo and more than one photo per page. Probably a random method is sufficient.
source to share
For each page, do this (for example, for page 3, page size 10):
select ...
from ...
where has_photo
order by created
limit 3, 1
union
select ...
from ...
where not has_photo
order by created
limit 27, 9
This query splits the two types of strings into two separate queries that are concatenated.
source to share