1

Drupal pager disappears when I add GROUP BY to the query.

This code works.

$countSql = "SELECT  COUNT(*) FROM journal ";
$sql = "SELECT  * FROM journal";    

$result = pager_query($sql, 1, 0, null, $countSql);
theme_pager(NULL, 10)

This code doesn't show the pager.

$countSql = "SELECT  COUNT(*) FROM journal ";
$sql = "SELECT  * FROM journal GROUP BY id";

$result = pager_query($sql, 1, 0, null, $countSql);  
theme_pager(NULL, 10)

What is it wrong with the latter code?

4

Your query with the GROUP BY is incorrect, MySQL is lax enough to let you write this kind of queries but it is by no way recommended. When you use a GROUP BY clause you should group all the columns without an aggregate function.

Also if you put a GROUP BY in your query, you should also include it in your count query. Generally a brutal way to make the count query is to put it this way:

$sql = "SELECT  * FROM journal";
$countSql = "SELECT  COUNT(*) FROM ($sql) ";

Try to modify your queries and amend your question with further precisions.

|improve this answer|||||
  • Thanks for your reply, u r right in sql server i cannot use group by like this but mysql let me do this,also that is not actual query i m going to post my actual query in my own answer – Kamran Akhter Dec 27 '11 at 11:09
  • If you want, you can correct this behaviour by secifying the option ONLY_FULL_GROUP_BY in MySQL configuration. – tostinni Dec 27 '11 at 15:07
1

I need distinct journals, and their IDs must be in article table; the article table can have multiple journal IDs, and I need two article columns.

SELECT  j.nid AS journal_nid, j.title AS journal_title, j.id AS journal_id, a.is_epub, a.nid AS article_nid, j.image_file_name
FROM journal j
INNER JOIN article a ON j.id = a.journal_id ". $condition." GROUP BY j.id ORDER BY j.title ASC

I modified my query, and the pager appears.

SELECT  j.id AS journal_id, j.nid AS journal_nid, j.title AS journal_title, j.image_file_name,
(select max(nid) from article where journal_id = j.id) AS article_nid,
(select max(is_epub) from article where journal_id = j.id) is_epub
FROM journal j
WHERE j.id IN (SELECT journal_id FROM article)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.