LazyDataModel and a complex key: is it possible?

[j4nd3r53n]

Sorry if the subject is a little unclear - here is what I am trying to do. I have a MySQL table:

Code: Select all

mysql> desc attributes;
+---------+--------------+------+-----+---------------------+----------------+
| Field   | Type         | Null | Key | Default             | Extra          |
+---------+--------------+------+-----+---------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL                | auto_increment |
| parent  | int(11)      | YES  | MUL | 1                   |                |
| type    | int(11)      | YES  | MUL | 1                   |                |
| name    | varchar(128) | YES  |     | NULL                |                |
| created | timestamp    | NO   | MUL | CURRENT_TIMESTAMP   |                |
| changed | timestamp    | NO   | MUL | 0000-00-00 00:00:00 |                |
+---------+--------------+------+-----+---------------------+----------------+

I want to load it into a dataTable, using pagination and the LazyDataModel, like in the https://www.primefaces.org/showcase/ui/ ... lazy.xhtml. However, the example there is based on a simple, numeric, unique key, and I want to sort on a complex key, like in:

Code: Select all

mysql> select * from attributes order by changed desc,id;
+------+--------+------+-----------------------------+---------------------+---------------------+
| id   | parent | type | name                        | created             | changed             |
+------+--------+------+-----------------------------+---------------------+---------------------+
|    1 |      1 |    1 | root                        | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
|    2 |      1 |    1 | Item property name          | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
...

I can achieve the desired effect in MySQL with a statement like this:

Code: Select all

mysql> select * from attributes where changed <= "2018-01-17 13:42:03" and id >100 order by changed desc, id asc limit 5;
+-----+--------+------+-----------------+---------------------+---------------------+
| id  | parent | type | name            | created             | changed             |
+-----+--------+------+-----------------+---------------------+---------------------+
| 101 |      1 |    2 | Client type     | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
| 102 |      1 |    2 | Document source | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
| 500 |      1 |    4 | application     | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
| 501 |      1 |    4 | audio           | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
| 502 |      1 |    4 | chemical        | 2018-01-17 13:42:03 | 2018-01-17 13:42:03 |
+-----+--------+------+-----------------+---------------------+---------------------+
5 rows in set (0.00 sec)

You can see how one could select 1 line more than the page length (by setting LIMIT to pagelen + 1, or in JPA, using setMaxResults) to get the key for the first row of the next page, then use those values to start the next query off. But I'm not sure how to translate this into the setup with the LazyDataModel - maybe it isn't possible?

[kukeltje]

I see three parts in your question, non actually related to the title of the question.

First of all, you don't seem to have a 'complex' key, the id itself seems to be unique, so I fail to see that part of the title.

The second part of your question seems to be related to sorting on multiple fields/columns. That is possible since you can (should) implement that query part yourself in your LazyDataModel. You can even use multiple fields from the datatable.

The third part sort of indicates you want to implement paging differently, either by not using the need to find the total numbers of rows (there is a 'patch' for that, need to look it up) or that you just want to optimize the offset/limit queries like mentioned here: https://www.eversql.com/faster-paginati ... t-is-slow/
(or both).

Care to elaborate? (But I think what you want can be achieved, how to do it is also effectively in that blog.)...

See also: https://github.com/primefaces/primefaces/issues/2581

[j4nd3r53n]

Hi kukeltje,

Thx for taking the time

Yes, I know, I realised soon after I clicked submit, that the combination of the ID and CREATED was redundant; I wasn't really focusing on that part. What I am after is actually the question in the subject: How can I page through a table, if I don't have a simple, numeric primary key? Say:

Code: Select all

CREATE TABLE `item_attributes` (
  `item_id` int(11) DEFAULT NULL,
  `attribute_id` int(11) DEFAULT NULL,
  `svalue` varchar(128) DEFAULT NULL,
  `ivalue` int(11) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `changed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`item_id`,`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# Example of a SELECT:

"select * from item_attributes where item_id>%it and attribute_id>%at order by item_id,attribute_id limit %rows"

From what I can see in the showcase, there are only two variants of the load() member function, both of which require an Integer as the first parameter - I understand this is meant to be the row offset or similar of the select statement, but that does not scale well, so I was hoping to find a way to specify a 'start-key', like I have suggested in the last of my examples. It could be kludged together by overloading the load() with a String as the first parameter, and some ad-hoc syntax along the lines of "FirstKey:SecondKey:...", which could then be parsed to supply the individual search criteria; in the example above, a start value for 'item_id > %it' and another to put into 'attribute_id > %at'. I would have done that already, but I suspect I will have to dive into whatever lies behind the LazyDataModel and the whole p:dataTable tag, and I don't quite feel up to that.