Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » @OrderBy problem

Show: Today's Messages :: Show Polls :: Message Navigator

@OrderBy problem [message #816907]

Fri, 09 March 2012 12:11

Pavel Zelenka

Messages: 61
Registered: July 2009

Member

Hi,

I would appreciate any help with this problem:

Let's assume I have three entities: Order, Item and Type (one order can contain number of items. Every item holds a Type reference)
I'd like the itemList collection to be sorted by "type.code". But the @OrderBy("type.code") annotation allways throw QueryException : Invalid query key [code] in expression

Can anybody help?

Thanks

@Entity
public class Order
@Id
@Column(name = "order_id")
private Integer orderId;
@Column(name = "order_date")
@Temporal(TemporalType.DATE)
private Date orderDate
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
@OrderBy("type.code")
private List<Item> itemList
....

@Entity
public class Type
@Id
@Column(name = "type_id")
private Integer typeId;
@Column(name = "type_code")
private Integer code
....

@Entity
public class Item
@Id
@Column(name = "item_id")
private Integer itemId;
@Column(name="item_text")
private String itemText;
@Column(name="item_value")
private Integer itemValue;
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "order_id", referencedColumnName = "order_id")
private Order order
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "type_id", referencedColumnName = "type_id")
private Type type

Report message to a moderator

Re: @OrderBy problem [message #817068 is a reply to message #816907]

Fri, 09 March 2012 15:44

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

The specification states that "A property or field name specified as an orderby_item must correspond to a basic persistent property
or field of the associated class or embedded class within it", so you cannot use type.code since it does not represent a property in Item.

Please file a feature request if you wish to have this added to EclipseLink as an extension to JPA.

Report message to a moderator

Re: @OrderBy problem [message #818925 is a reply to message #817068]

Mon, 12 March 2012 09:35

Pavel Zelenka

Messages: 61
Registered: July 2009

Member

Is there a possibility to achieve this sorting via native query?

Report message to a moderator

Re: @OrderBy problem [message #825186 is a reply to message #818925]

Tue, 20 March 2012 15:25

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

You can use a DescriptorCustomizer to customize the mapping to add an Expression for the ordering.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: @OrderBy problem [message #1711582 is a reply to message #825186]

Sat, 17 October 2015 18:18

Hartmut P

Messages: 1
Registered: October 2015
Location: Germany

Junior Member

Thank You, James - that hint was very helpful. A DescriptorCustomizer can be used to achieve the requirement. In my case, I needed something similar to "JPQL Path Expressions", to navigate through Entities and related objects, for getting a @OneToMany list sorted. (hint: native @orderBy(...) is only applicable to more trivial cases, as far as I know, no path expressions / navigation allowed there)

@Entity
@Table(name = BmAccount.bm_account)
@Customizer(BmAccountE.BmAccountECustomizer.class)
public class BmAccountE implements Serializable {

	public static class BmAccountECustomizer implements DescriptorCustomizer {
		@Override
		public void customize(ClassDescriptor descriptor) throws Exception {
			OneToManyMapping o2m = ((OneToManyMapping) descriptor
			   .getMappingForAttributeName("accountUsers"));
			ReadAllQuery raq = (ReadAllQuery) o2m.getSelectionQuery();
			raq.addOrdering(raq.getExpressionBuilder()
			   .get("user").get("userName").toUpperCase().ascending());
		}
	}
...

Report message to a moderator

Previous Topic:	Unable to generate Canonical Metamodel
Next Topic:	Wrong SQL using COALESCE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 15 03:35:35 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter