Complex order by in JPQL

3
votes

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;
}
2
jpajpql
Please show your entities (the associations between them). – Pascal Thivent

2 Answers

0
votes

You have several options:

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

  • Add err.task.src to select clause (select msg, err.task.src) and change return type in your method

  • Order in memory instead of bd, using interface Comparable (implements Comparable and use Collections.sort)

  • Also you could use criteriaQuery or nativeQuery

0
votes

If your only problem is to order the result list, then one way to do it is to add @OrderBy on the global fields (default order is ASC which can be left out):

@OrderBy("YOUR_COLUMN_NAME")

If you want descending order, the syntax will be:

@OrderBy("YOUR_COLUMN_NAME DESC")

For example: 

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

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "SRC_ID")
@OrderBy("SRC_ID DESC") // <-------- DESC
private CommonSource src;