Pagination query changed after 2.9
Developer Preview
Background: Publisher has a setting called “persistence” meaning that each entry has to have a translation, and if it doesn’t it uses the default language values for other languages. When this is turned off it allows the content trees in languages to differ. E.g. English can have 10 entries in a channel and another language can have 5. So its non-persistent. To make pagination work in Publisher I had to look at the ee()->db->queries array, find the one that is used to get the full collection of entries so it can get total count, I believe this was used to figure out how many entries are in the results and how many pages exist. Basically these little string replace used to work:
$sql = str_replace(
'WHERE',
'LEFT JOIN '. $tableName .' AS pt ON pt.entry_id = t.entry_id
WHERE pt.publisher_lang_id = ' . $publisher_lang_id . '
AND pt.publisher_status = "' . $publisher_status . '" AND ',
$query
);// Remove the offset and limit so we can grab the count and update the pagination object.
$sql = preg_replace('/LIMIT \d+, \d+/', '', $sql);Which resulted in a new count, then I could change the pagination count so it would paginate over 5 entries instead of 10.
Sometime after EE 2.9 the query no longer contains a limit. Thats done sometime earlier, and the query now looks like this:
SELECT t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id,
w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled,
m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio,
md.*,
wd.*
FROM exp_channel_titles AS t
LEFT JOIN exp_channels AS w ON t.channel_id = w.channel_id
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id
LEFT JOIN exp_members AS m ON m.member_id = t.author_id
LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id WHERE t.entry_id IN (70912,70881,70880,70864,70862) ORDER BY t.sticky desc, t.entry_date desc, t.entry_id descSo it’s already determined what entries to grab, so I can’t figure out what the total count is if the entries are not persistent across languages.
I’m going to start running diffs, but does anyone at EL recall any changes to the queries in the pagination/entries class after 2.9?
Ok, for now disregard this. After debugging a bit I’m onto something…
There are two main queries that run in build_sql_query() for the Channel Entries tag. The first runs with limits imposed by the limit and pagination parameters, and gets the entry IDs. The second runs to fetch all of the meta data and custom field data, using the WHERE...IN() clause in your example query.
If you turn on the profiler, you can find these queries easily by doing a page find for build_sql_query(). Here are two examples from a basic entries tag:
SELECT t.entry_id
FROM exp_channel_titles AS t
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id
LEFT JOIN exp_members AS m ON m.member_id = t.author_id
WHERE t.entry_id !=''
AND t.site_id IN ('1')
AND t.entry_date < 1469472721
AND (t.expiration_date = 0 OR t.expiration_date > 1469472721)
AND t.channel_id IN (1)
AND t.status = 'open'
ORDER BY t.sticky desc, t.entry_date desc, t.entry_id desc
LIMIT 0, 5And:
SELECT t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id,
w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled,
m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio,
md.*,
wd.*
FROM exp_channel_titles AS t
LEFT JOIN exp_channels AS w ON t.channel_id = w.channel_id
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id
LEFT JOIN exp_members AS m ON m.member_id = t.author_id
LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id
WHERE t.entry_id IN (10,9,7,6,5)
ORDER BY t.sticky desc, t.entry_date desc, t.entry_id descI would definitely consider these queries private API and would strongly discourage relying on them—the method we use to fetch the entry Channel data does not have an entrance point for modifying them, and they could be subjected to internal changes without notice or backwards compatibility.
That said, the two queries here have not changed in the basic logic and structure in ages—are you not seeing the LIMIT in the first query as shown above?
Turned out to be an issue in Publisher. Yeah, I know using the query string like that is far less than ideal, but without another hook there is no way to grab that information :/
It sounds like your schema is already tracking entry IDs, can it not fetch this information separately, all at once for all entries returned by the Channel Entries tag?