11:18 p.m.
Criteria API : Order by Native SQL
-----------------------------------
Key: HHH-2381
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381
Project: Hibernate3
Type: Improvement
Components: query-criteria
Reporter: Sami Dalouche
Hi,
It would be nice to be able to specify the ORDER BY clause using Native SQL. (It is
currently doable by subclassing the Order class, but some nice solution would allow the
order to specify attributes on another table..)
Let's take a simple example : I have a distance("attribute", geometry)
function, that is of course, proprietary.
I am quering table A, which I can join to table B to get the "attribute", on
which I want to apply my distance() function.
If I create my own Order subclass, and addOrder on the criteria, the
distance("attribute, geometry) is going to refer to the main criteria's class
mapping.
So, something like addOrder("objectpath.joined", new MyOrder(...)); would be
perfect.
What do you think ?
Regards,
Sami Dalouche
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
6:08 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2381) Criteria API : Order by Native SQL
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381?page=c...
]
Sami Dalouche commented on HHH-2381:
------------------------------------
OK, Here is a sample implementation of the Order class that can be used to produce Native
SQL ORDER BY Clauses :
public class NativeSQLOrder extends Order {
private static final long serialVersionUID = 1L;
private final static String PROPERTY_NAME = "uselessAnyways";
private boolean ascending;
private String sql;
protected NativeSQLOrder(String sql, boolean ascending) {
super(PROPERTY_NAME, ascending);
this.sql = sql;
this.ascending = ascending;
}
@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
StringBuilder fragment = new StringBuilder();
fragment.append("(");
fragment.append(sql);
fragment.append(")");
fragment.append(ascending ? " asc" : " desc");
return StringHelper.replace(fragment.toString(), "{alias}",
criteriaQuery.getSQLAlias(criteria));
}
}
criteria.addOrder(new Order("whatever SQL using {alias}", true));
Regards,
Sami Dalouche
Criteria API : Order by Native SQL
-----------------------------------
Key: HHH-2381
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381
Project: Hibernate3
Issue Type: Improvement
Components: query-criteria
Reporter: Sami Dalouche
Hi,
It would be nice to be able to specify the ORDER BY clause using Native SQL. (It is
currently doable by subclassing the Order class, but some nice solution would allow the
order to specify attributes on another table..)
Let's take a simple example : I have a distance("attribute", geometry)
function, that is of course, proprietary.
I am quering table A, which I can join to table B to get the "attribute", on
which I want to apply my distance() function.
If I create my own Order subclass, and addOrder on the criteria, the
distance("attribute, geometry) is going to refer to the main criteria's class
mapping.
So, something like addOrder("objectpath.joined", new MyOrder(...)); would be
perfect.
What do you think ?
Regards,
Sami Dalouche
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:11 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2381) Criteria API : Order by Native SQL
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381?page=c...
]
Taro App commented on HHH-2381:
-------------------------------
I have another use case in which this functionality would be very useful.
I have Product entity, which is stored in Second Level Cache because product data is
needed frequently.
I also have product query form. The result can be sorted by several options such as by
item name, by sales, etc.
To sort by item name, it is as easy as this:
criteria.addOrder(Order.asc('itemName'))
Sortint by sales is not as easy. Derived property is not a good solution here, because I
don't want sales to be cached.
It would be as easy as this if native sql order by is supported:
criteria.addOrder(NativeSQLOrder.asc("select sum(o.sales) from orders o where
o.item_id = item_id"));
I also have more cases which needs native sql order by functionality:
- Sorting Product by number of Bids places for that Product.
- Sorting Product by ProductCategory display order in which needs Oracle Hierarchy Query
Criteria API : Order by Native SQL
-----------------------------------
Key: HHH-2381
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381
Project: Hibernate Core
Issue Type: Improvement
Components: query-criteria
Reporter: Sami Dalouche
Hi,
It would be nice to be able to specify the ORDER BY clause using Native SQL. (It is
currently doable by subclassing the Order class, but some nice solution would allow the
order to specify attributes on another table..)
Let's take a simple example : I have a distance("attribute", geometry)
function, that is of course, proprietary.
I am quering table A, which I can join to table B to get the "attribute", on
which I want to apply my distance() function.
If I create my own Order subclass, and addOrder on the criteria, the
distance("attribute, geometry) is going to refer to the main criteria's class
mapping.
So, something like addOrder("objectpath.joined", new MyOrder(...)); would be
perfect.
What do you think ?
Regards,
Sami Dalouche
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:54 p.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2381) Criteria API : Order by Native SQL
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381?page=c...
]
Ondrej Medek updated HHH-2381:
------------------------------
Attachment: NativeSQLOrder.java
Hi, thanks for your NativeSQLOrder. I have improved it for alias substiution. Now you can
write something like NativeSQLOrder.asc("{person.name}||'
'||{person.surname}");
Criteria API : Order by Native SQL
-----------------------------------
Key: HHH-2381
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381
Project: Hibernate Core
Issue Type: Improvement
Components: query-criteria
Reporter: Sami Dalouche
Attachments: NativeSQLOrder.java
Hi,
It would be nice to be able to specify the ORDER BY clause using Native SQL. (It is
currently doable by subclassing the Order class, but some nice solution would allow the
order to specify attributes on another table..)
Let's take a simple example : I have a distance("attribute", geometry)
function, that is of course, proprietary.
I am quering table A, which I can join to table B to get the "attribute", on
which I want to apply my distance() function.
If I create my own Order subclass, and addOrder on the criteria, the
distance("attribute, geometry) is going to refer to the main criteria's class
mapping.
So, something like addOrder("objectpath.joined", new MyOrder(...)); would be
perfect.
What do you think ?
Regards,
Sami Dalouche
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:05 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2381) Criteria API : Order by Native SQL
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381?page=c...
]
Rico commented on HHH-2381:
---------------------------
Is it possible to refactor Order to simplify writing db specific Order class (typically
for NLS_SORT in Oracle)? For instance, by changing the visibility of fields ascending,
ignoreCase & propertyName (protected instead of private) ;
Criteria API : Order by Native SQL
-----------------------------------
Key: HHH-2381
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381
Project: Hibernate Core
Issue Type: Improvement
Components: query-criteria
Reporter: Sami Dalouche
Attachments: NativeSQLOrder.java
Hi,
It would be nice to be able to specify the ORDER BY clause using Native SQL. (It is
currently doable by subclassing the Order class, but some nice solution would allow the
order to specify attributes on another table..)
Let's take a simple example : I have a distance("attribute", geometry)
function, that is of course, proprietary.
I am quering table A, which I can join to table B to get the "attribute", on
which I want to apply my distance() function.
If I create my own Order subclass, and addOrder on the criteria, the
distance("attribute, geometry) is going to refer to the main criteria's class
mapping.
So, something like addOrder("objectpath.joined", new MyOrder(...)); would be
perfect.
What do you think ?
Regards,
Sami Dalouche
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4059
days inactive
6301
days old
hibernate-issues@lists.jboss.org
10 comments
4 participants
participants (4)
-
Ondrej Medek (JIRA) -
Rico (JIRA)
-
Sami Dalouche (JIRA)
-
Taro App (JIRA)