Getting closer - I have set up a custom dialect:
package org.vectorbase.funcgen.hibernate;
import org.hibernate.Hibernate;
import org.hibernate.dialect.MySQLInnoDBDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
public class CustomDialect extends MySQLInnoDBDialect {
public CustomDialect() {
super();
registerFunction("srand", new StandardSQLFunction("rand",
Hibernate.DOUBLE) );
}
}
configure this in base.config
And it all goes fine until some tmp tables run out of disk space or something
(We have around 400k reporters FYI.)
Looks like Nicklas has seen this before :-)
https://forum.hibernate.org/viewtopic.php?f=1&t=994753
I reckon I'll get it to work sooner or later.
cheers,
Bob.
On Tue, Oct 9, 2012 at 2:15 PM, Bob MacCallum
<r.maccal...@imperial.ac.uk> wrote:
> Hi Nicklas,
> Thanks very much for the quick reply. I am *so* close to getting this
> working - it seems that the argument-less rand() is available in HQL
> but I will have to jump through a few hoops to provide the seeded
> version.
> I'll post an update later...
> cheers,
> Bob.
>
> On Tue, Oct 9, 2012 at 12:50 PM, Nicklas Nordborg <nick...@thep.lu.se> wrote:
>> On 2012-10-09 13:11, Bob MacCallum wrote:
>>> Hi all,
>>>
>>> I'm trying to get a Reporter query from the BASE2.x API which is
>>> basically "ORDER BY RAND(123)" - to get reproducible "random" order (I
>>> can explain *why* in another email if anyone cares to know).
>>>
>>> I tried this
>>>
>>> DataQuery<net.sf.basedb.core.data.ReporterData> repQuery =
>>> net.sf.basedb.core.Reporter.getQuery();
>>>
>>> repQuery.order(Orders.asc(new HqlExpression("RAND(123)", "xxx")));
>>>
>>> But it seems that HqlExpression isn't for public use. I can't find
>>> something like "Expressions.hql".
>>
>> This can be found in net.sf.basedb.core.query.Hql.expression(...)
>>
>>> Could somebody please save me a ton of time and tell me how to put
>>> some arbitrary SQL into the ORDER BY clause of a DataQuery?
>>
>> Note that it is not SQL that is needed but HQL (Hibernate Query
>> Language). Don't know if RAND() is available in HQL. If not, it should
>> be possible to create a custom Hibernate dialect and use that in the
>> 'db.dialect' setting in base.config. I have never done that so I don't
>> know any details or how hard/easy it is.
>>
>> Another possibility is to use Reporter.getDynamicQuery() instead which
>> lets you work with SQL, but code-wise it's a bit harder to work with
>> since you have to process the result row-by-row and column-by-column
>> instead of as ReporterData objects.
>>
>> /Nicklas
>>
>>
>> ------------------------------------------------------------------------------
>> Don't let slow site performance ruin your business. Deploy New Relic APM
>> Deploy New Relic app performance management and know exactly
>> what is happening inside your Ruby, Python, PHP, Java, and .NET app
>> Try New Relic at no cost today and get our sweet Data Nerd shirt too!
>> http://p.sf.net/sfu/newrelic-dev2dev
>> _______________________________________________
>> The BASE general discussion mailing list
>> basedb-users@lists.sourceforge.net
>> unsubscribe: send a mail with subject "unsubscribe" to
>> basedb-users-requ...@lists.sourceforge.net
------------------------------------------------------------------------------
Don't let slow site performance ruin your business. Deploy New Relic APM
Deploy New Relic app performance management and know exactly
what is happening inside your Ruby, Python, PHP, Java, and .NET app
Try New Relic at no cost today and get our sweet Data Nerd shirt too!
http://p.sf.net/sfu/newrelic-dev2dev
_______________________________________________
The BASE general discussion mailing list
basedb-users@lists.sourceforge.net
unsubscribe: send a mail with subject "unsubscribe" to
basedb-users-requ...@lists.sourceforge.net
