Wrong records are being showed with the Fields Plugin

Question

I have the fields plugin to display my books in the index.gsp view. My book domain have a publishedDate Date field.

<f:table collection="${bookList}"/>
<div class="pagination">
     <g:paginate total="${bookCount ?: 0}" />
</div>

The plugin will show my books in the table 10 rows at a time. The problem is if I click the Published Date column header to sort my books, some of the books will be missing from the table.

I try to troubleshoot the problem. I have 8 books that were published at the same date. So, I change the book titles to just 1,2,3, up to 8. So, I will see which books are missing.

The plugin shows book 1,4,2,3,5 in the first page, and book 8 and 5 in the second page in this order.

Book 6 is missing. Book 5 is being showed twice in page 1 and page 2.

If I click the column again to sort my books in descending order, book 4, 5, 6, 1 are showed in the second last page, and 2, 3, 5, 4, 6 are showed in the last page. Book 4, 5, and 6 are being showed twice.

If I don't click any column to sort the rows, all 8 books will be showed without any problem.

How to troubleshoot this wrong records problem?

Answer 1

The problem is if I click the Published Date column header to sort my books, some of the books will be missing from the table.

There really is no good way to know for sure what is going wrong in your app with just that information. It is likely that your app is configured so when you click the column header a request is being made to the server which will create a model which will drive the view. There are any number of things that might be wrong with that code.

How to troubleshoot this wrong records problem?

I would inspect the model that is being generated when you click the column header and the back from there into the query that is retrieving the records. That will likely highlight was is going wrong.

Answer 2

I have tracked down the source of the problem. The problem is with the LIMIT offset, max SQL statement.

This is my troubleshooting steps.

I am using Heidisql. I can also click the header to sort the records in Heidisql. Heidisql will show the SQL statement. This is the SQL.

SELECT * FROM `book` ORDER BY `published_date` ASC LIMIT 1000;

The records come back in the order I am expecting. So, I track the SQL sent from the fields plugin when I click the header column and the Next button. This is basically what the plugin send to the database.

SELECT * FROM `book` ORDER BY `published_date` ASC LIMIT 10,10

So, I copy and paste this SQL to Heidisql to see what the records will look like. Surprisingly, the records come back all messed up too.

Now I know what the problem is. I cannot sort my records just by using a single column. This is what the plugin sent to the database. There is not enough information to sort the records correctly. The ASC LIMIT statement makes a best guess to sort the records in this case. That is why the records come back wrong.

I will need to make the plugin to send more than 1 column to help the sorting.