Pager disappears when adding "GROUP BY" in the SQL query

Question

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?

Answer 1

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.

Answer 2

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)