Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » @OrderBy problem
@OrderBy problem [message #816907] Fri, 09 March 2012 12:11 Go to next message
Pavel Zelenka is currently offline Pavel ZelenkaFriend
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
Re: @OrderBy problem [message #817068 is a reply to message #816907] Fri, 09 March 2012 15:44 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1195
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.
Re: @OrderBy problem [message #818925 is a reply to message #817068] Mon, 12 March 2012 09:35 Go to previous messageGo to next message
Pavel Zelenka is currently offline Pavel ZelenkaFriend
Messages: 61
Registered: July 2009
Member
Is there a possibility to achieve this sorting via native query?
Re: @OrderBy problem [message #825186 is a reply to message #818925] Tue, 20 March 2012 15:25 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
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.
Re: @OrderBy problem [message #1711582 is a reply to message #825186] Sat, 17 October 2015 18:18 Go to previous message
Hartmut P is currently offline Hartmut PFriend
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());
		}
	}
...
Previous Topic:Unable to generate Canonical Metamodel
Next Topic:Wrong SQL using COALESCE
Goto Forum:
  


Current Time: Sun Dec 04 06:12:00 GMT 2016

Powered by FUDForum. Page generated in 0.13675 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software