Home Complex order by in JPQL
Reply: 0

Complex order by in JPQL

user2664
1#
user2664 Published in June 25, 2018, 12:02 am

Let me start by saying I'm not a DB guy. This stuff just confuses me to death, but unfortunately I got roped into doing this at work, so I'm kinda stuck :-)

I'm trying to get a complex order by to work in JPQL, and I'm not having any luck at all.

My current query looks like this:

select distinct msg from CSMessage msg, Error err where msg = err.msg order by err.task.src

What I'm trying to accomplish with this is to get all the msgs's with related errors, then sort the whole thing using the source document (err.task.src), to get all errored messages with the same source to appear together.

Needless to say this doesn't work at all. I get an exception that says; "ORDER BY item should be in the SELECT DISTINCT list"

I've looked over the docs and other sources and there doesn't seem to be anything in there that can help me.

Can anyone point me in the right direction?

Thanks

Edit 1:

The entities look like this:

CSMessage

public class CSMessage extends BaseModel implements Serializable
{

    private static final long serialVersionUID = 1L;

        .
        .
        .
        Other fields not shown for brevity
        .
        .
        .
    @ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinColumn(name = "TASK_ID")
    private Task task;
}

Error:

public class Error
{

    private static final long serialVersionUID = 1L;

    @Column(name = "ERR_STRING", length = 255)
    private String errString;

    @Column(name = "ERR_TYPE")
    private Integer errType;

    @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinColumn(name = "MSG_ID")
    private CSMessage msg;

    @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinColumn(name = "SRC_ID")
    private CommonSource src;

    @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    @JoinColumn(name = "TASK_ID")
    private Task task;
}

Task

public class Task
{

    private static final long serialVersionUID = 1L;

    @Column(name = "CORRELATION_UUID", length = 36)
    private String correlationId;

    @Column(name = "CURRENT_NODE", length = 255)
    private String currentNodeName = "empty";

    @Column(name = "PROCESS_NAME", length = 255)
    private String processName = "empty";

    @Column(name = "SITE_ID", length = 10)
    private String siteId = "1";

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SRC_ID")
    private CommonSource src;
}

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.321383 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO