{"id":141,"date":"2016-12-20T11:26:00","date_gmt":"2016-12-20T10:26:00","guid":{"rendered":"http:\/\/xpam.pl\/blog\/?p=141"},"modified":"2024-04-02T23:53:44","modified_gmt":"2024-04-02T21:53:44","slug":"jpql-getting-whole-entities-on-a-distinct-column","status":"publish","type":"post","link":"https:\/\/xpam.pl\/blog\/?p=141","title":{"rendered":"JPQL: getting whole entities on a distinct column"},"content":{"rendered":"<p>If you want distinct entities in JPQL you would normally write something like this:<\/p>\n<pre><code class=\"language-sql\">SELECT DISTINCT(l) FROM Letter l WHERE l.name=:name;<\/code><\/pre>\n<p>But this will do a DISTINCT on the whole Entity. This will also fail on Oracle DB if your Entity contains a CLOB. What if you really want to do a DISTINCT on a field, for example:<\/p>\n<pre><code class=\"language-sql\">SELECT DISTINCT(l.id) FROM Letter l WHERE l.name=:name;<\/code><\/pre>\n<p>Unfortunately, this only returns an array of ID fields. If you want to retrieve the full entities and do a DISTINCT on a field the final query looks like:<\/p>\n<pre><code class=\"language-sql\">SELECT ll FROM Letter ll WHERE ll.id IN (SELECT DISTINCT(l.id) FROM Letter l WHERE l.name=:name);<\/code><\/pre>\n<div class=\"wp-post-signature\">\r\n<br \/>\r\n<br \/>\r\n<img src='https:\/\/xpam.pl\/aaaaff.png' title='Moonie' \/> Cen<br \/>\r\n<a href='https:\/\/github.com\/cen1'>GitHub<\/a><br \/>\r\n<a href='https:\/\/eurobattle.net'>Eurobattle.net<\/a><br \/>\r\n<a href='https:\/\/lagabuse.com'>Lagabuse.com<\/a><br \/>\r\n<a href='https:\/\/bnetdocs.org'>Bnetdocs<\/a><br \/>\r\n<\/div>\r\n","protected":false},"excerpt":{"rendered":"<p>If you want distinct entities in JPQL you would normally write something like this: SELECT DISTINCT(l) FROM Letter l WHERE l.name=:name; But this will do a DISTINCT on the whole Entity. This will also fail on Oracle DB if your Entity contains a CLOB. What if you really want to do a DISTINCT on a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-141","post","type-post","status-publish","format-standard","hentry","category-programming"],"_links":{"self":[{"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=\/wp\/v2\/posts\/141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=141"}],"version-history":[{"count":4,"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=\/wp\/v2\/posts\/141\/revisions"}],"predecessor-version":[{"id":516,"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=\/wp\/v2\/posts\/141\/revisions\/516"}],"wp:attachment":[{"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xpam.pl\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}