JPA. JOIN nested SELECT "unexpected token ("

Question

I have News entity and want to get list of news ordered by number of comments. I try to do it like this:

Query query = manager.createQuery("SELECT n FROM News n LEFT JOIN \n" +
                "(SELECT c.newsId, COUNT(c.newsId) as commentCount FROM COMMENTS c GROUP BY c.newsId) c ON n.newsId=c.newsId" +
                "ORDER BY c.commentCount DESC NULLS LAST, n.modificationDate DESC");

But I have "unexpected token: (" after LEFT JOIN. I tried to write different queries, but didn't succeeded. Help me please to fix this query.

News.java:

    @Entity
    @Table(name = "NEWS")
    public class News implements Serializable{
        /**
         * serialVersionUID
         */
        private static final long serialVersionUID = 883279937885116359L;
        /**
         * News id
         */
        @Id
        @GeneratedValue(generator = "seq")
        @SequenceGenerator(name="seq", sequenceName="NEWS_SEQ",allocationSize=1)
        @Column(name = "NEWS_ID", nullable = false, unique = true)
        private Long id;
        /**
         * News short text
         */
        @Column(name = "SHORT_TEXT")
        private String shortText;
        /**
         * News full text
         */
        @Column(name = "FULL_TEXT")
        private String fullText;
        /**
         * News title
         */
        @Column(name = "TITLE")
        private String title;
        /**
         * News creation date
         */
        @Column(name = "CREATION_DATE")
        private Date creationDate;
        /**
         * News modification date
         */
        @Column(name = "MODIFICATION_DATE")
        private Date modificationDate;
        /**
         * News author
         */
        @ManyToOne(cascade = CascadeType.ALL)
        @JoinTable(
                name = "NEWS_AUTHOR",
                joinColumns = {@JoinColumn(name = "NEWS_ID")},
                inverseJoinColumns =  {@JoinColumn(name = "AUTHOR_ID")}
        )
        private Author author;
        /**
         * News tags
         */
        @OneToMany(cascade = CascadeType.ALL)
        @JoinTable(
                name = "NEWS_TAG",
                joinColumns = {@JoinColumn(name = "NEWS_ID")},
                inverseJoinColumns =  {@JoinColumn(name = "TAG_ID")}
        )
        private List<Tag> tags;
        /**
         * News comments
         */
        @OneToMany(cascade = CascadeType.ALL)
        @JoinColumn(name = "NEWS_ID")
        private List<Comment> comments;
...

Comment.java:

    @Entity
    @Table(name = "COMMENTS")
    public class Comment implements Serializable{
        /**
         * serialVersionUID
         */
        private static final long serialVersionUID = -5697896094322498108L;
        /**
         * Comment id
         */
        @Id
        @GeneratedValue(generator = "seq")
        @SequenceGenerator(name="seq", sequenceName="COMMENTS_SEQ",allocationSize=1)
        @Column(name = "COMMENT_ID", nullable = false, unique = true)
        private Long id;
        /**
         * Comment text
         */
        @Column(name = "COMMENT_TEXT")
        private String commentText;
        /**
         * Comment creation date
         */
        @Column(name = "CREATION_DATE")
        private Date creationDate;
        /**
         * Id of the news which the comment is added to
         */
        @Column(name = "NEWS_ID")
        private Long newsId;
...

Answer 1

If you just want to order news by number of comments, this should do it

select n from News n order by size(n.comments)