Is this a good way to select the latest post?

Question

I'm using a subselect to select the latest post. Is that a good idea?

@Query("SELECT p FROM Post p INNER JOIN p.user WHERE p.user.username = :username AND p.updated = (SELECT MAX(p.updated) FROM Post p WHERE p.user.username = :username)")
Post findLatestByUsername(@Param("username") String username);

What is the difference between `p.created` and `p.updated`? If an older post has been updated after another was created (but not yet updated), should we select the older post or the newer post? — toonice, Apr 23 '17 at 09:04
@Vecchiasignora - postgresql in production, h2 for testing. But I'd prefer that not to be reflected in the query. — user672009, Apr 23 '17 at 09:12
seems to be a dupe of http://stackoverflow.com/questions/6708085/select-top-1-result-using-jpa — Philipp Merkle, Apr 23 '17 at 09:48

score -1 · Answer 1 · edited May 23 '17 at 12:34

-1

I think the best way to select latest row it using index descening

set username, updated columns as descening index if you dont have index on those columnes, for more information how to set it you can look link bellow

https://www.postgresql.org/docs/9.5/static/sql-createindex.html

then your select will be like this

SELECT * FROM Post p where p.user.username = :username LIMIT 1

your reading data will be already descening sortet after select, an then you can select 1 row and it will be latest row, data was decening sorted by username(and it is in where clause) and updated column

and about limit in HQL, you can solve this problem by other way please look links bellow

https://groups.google.com/forum/#!topic/nhibernate-development/JYE3NDcTUEA

How do you do a limit query in HQL?

LIMIT in Postgres not supported in HQL?

edited May 23 '17 at 12:34

Community

1
1

answered Apr 23 '17 at 09:28

Vecchiasignora

1,275
7
6

1. That is postgresql specific. 2. HQL doesn't feature limit. – user672009 Apr 23 '17 at 09:36
is there any way to select 1 row? – Vecchiasignora Apr 23 '17 at 09:37
Create a query which only returns one result. – user672009 Apr 23 '17 at 09:38
look answer again, i attached 3 links about limit – Vecchiasignora Apr 23 '17 at 09:48
That's not HQL. – user672009 Apr 23 '17 at 10:11

Is this a good way to select the latest post?

1 Answers1