Thread

Using EE v 1.6.8

When I click the “last page” link in my pagination, the page has no data returned. Every other paginated page shows results correctly. Basically, EE thinks there is 1 more page of results than there really is.

Here is the sql I’m using in my query tag:

SELECT DISTINCT member_id, avg, url_title, title, field_id_9, field_id_12,
field_id_13, field_id_14, field_id_21, field_id_24, field_id_46, field_id_47, 
username, screen_name FROM exp_weblog_data JOIN exp_weblog_titles ON 
exp_weblog_titles.entry_id = exp_weblog_data.entry_id JOIN exp_members ON
exp_members.member_id = exp_weblog_titles.author_id LEFT JOIN exp_rating_stats ON
exp_weblog_titles.entry_id = exp_rating_stats.entry_id WHERE exp_weblog_data.weblog_id = '1'
AND exp_weblog_titles.status = 'open' ORDER BY exp_weblog_titles.entry_date DESC

I have found that the issue is the DISTINCT . When I remove DISTINCT, the last page has results. The thing is, I need the DISTINCT in my sql query or otherwise my results have duplicates due to the exp_ratings table (solspace rating module) having multiple records per entry_id.

So basically with DISTINCT in the query, results are as they should be, no duplicates, but EE is counting the total pages as though the DISTINCT isn’t there (so it thinks there are more results than there actually is), so the extra page is tacked on.

Just as a test thinking that maybe EE doesn’t like the DISTINCT where it is, I re-wrote the query as such which worked in phpMyAdmin:

SELECT member_id, url_title, title, field_id_9, field_id_12, field_id_13, field_id_14,
field_id_21, field_id_24, field_id_46, field_id_47, username, screen_name FROM 
exp_weblog_data JOIN exp_weblog_titles ON exp_weblog_titles.entry_id = 
exp_weblog_data.entry_id JOIN exp_members ON exp_members.member_id = 
exp_weblog_titles.author_id LEFT JOIN (select distinct entry_id, avg from exp_rating_stats)
AS stats ON stats.entry_id = exp_weblog_titles.entry_id WHERE exp_weblog_data.weblog_id =
'1' AND exp_weblog_titles.status = 'open' ORDER BY exp_weblog_titles.entry_date DESC

but when EE ran this it throws the following error:

MySQL ERROR:

Error Number: 1054

Description: Unknown column 'stats.entry_id' in 'on clause'

Query: SELECT COUNT(*) AS count FROM exp_weblog_data JOIN exp_weblog_titles ON exp_weblog_titles.entry_id = exp_weblog_data.entry_id JOIN exp_members ON exp_members.member_id = exp_weblog_titles.author_id LEFT JOIN (SELECT COUNT(*) AS count FROM exp_rating_stats) AS stats ON stats.entry_id = exp_weblog_titles.entry_id WHERE exp_weblog_data.weblog_id = '1' AND exp_weblog_titles.status = 'open' ORDER BY exp_weblog_titles.entry_date DESC

I’m out of ideas. I was hoping the re-written query might work. I’m not sure what to do now.

I think the handling of DISTINCT in the query module may be a EE bug.

Please help.

anthonys,

Is there a reason why you are not using EE’s pagination?

EE Docs: Weblog Entry and Comment Pagination

I am using EE pagination.  Inside my query tag is the following:

{paginate}
<div class="paginate">
Page {current_page} of {total_pages} pages 
{pagination_links}


</div>
{/paginate}

I’m not certain whether this is related, but I was about to open a bug report about very similar issues with ‘GROUP BY’ and pagination (incorrect pagination links, not mySQL errors). The two do not seem to be willing to play nicely at all. I strongly suspect that this is a bug.

There’s another thread on the subject at http://ellislab.com/forums/viewthread/121900/

Here’s some test code I’ve been using to verify this:

{exp:query limit="30" sql="SELECT m.username, t.title FROM exp_members as m, exp_weblog_titles as t WHERE t.author_id = m.member_id GROUP BY m.username"}

{username} - {title}


{paginate}
Page {current_page} of {total_pages} pages {pagination_links}</td>
{/paginate}

{/exp:query}

If you modify the limit parameter and experiment with adding and removing ‘GROUP BY m.username’ you can see the problem in action. The site I’m using should return 3 results, but if I set the limit parameter to 3 I’m told that there are 13 pages of results instead of 1.

Have you seen this thread? The query module is really meant to handle rather simply SELECT type queries; subqueries have been known to cause issues. In your case using raw PHP, as it were, or the DB class directly might be the better option.

Ingmar, I just click your thread link and am presented with:

“The forum you are attempting to access is only available to ExpressionEngine users who have been invited to participate in the ExpressionEngine 2.0 Beta program. “

The forum you are attempting to access is only available to ExpressionEngine users who have been invited to participate in the ExpressionEngine 2.0 Beta program. If you are a Beta participant, please log in using the membership account you were invited with. If you require assistance please contact .(JavaScript must be enabled to view this email address).

If I need to use the DB class directly then so be it, but it does seem needlessly complicated to have to build my own pagination system just because I’ve used ‘group by’ in a query. Maybe that’s a feature request, if not a bug report.

Ingmar, I just click your thread link and am presented with:

“The forum you are attempting to access is only available to ExpressionEngine users who have been invited to participate in the ExpressionEngine 2.0 Beta program. “

I am sorry about that. Basically it’s a link to here.

Ooh, as that thread points out, a fix was implemented for the 20091201 build. What build is the site I’m working on? 20090915.

Five minutes later and it’s working perfectly. Cheers Ingmar.

I just updated my mod.query.php file with the changes in the latest build and the DISTINCT in my SQL query now works as it should.  Please go ahead and close this thread. Thanks again Dom.S

Yay! Glad things are working again. 😊 Don’t hesitate to post again as needed.