You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by William Wong <wi...@openwave.com> on 2004/01/19 02:32:16 UTC

Criteria question...

Hi All,

Is there any easy way to convert below Mysql select statement into a
Criteria statement?

select * from name order by rand() limit 1;

Can I use Criteria.CUSTOM to achieve it?

Thank you in advance.

Regards,
William


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


AW: Criteria question...

Posted by Michael Burschik <Bu...@lotto-berlin.de>.
This problem seems to pop up every few months. Can't some maintainer patch
the distributed source to allow for something used this often? Especially
since the change is so small.

Regards

Michael Burschik

> -----Ursprungliche Nachricht-----
> Von: Andres Quijano - Rizonet [mailto:andres@rizo.net]
> Gesendet: Dienstag, 20. Januar 2004 18:25
> An: 'Apache Torque Users List'
> Betreff: RE: Criteria question...
>
>
> now that I recall, I had to patch BasePeer... I think it was (but not
> for sure, check old posts)
>
> line 1220
>
> for (int i = 0; i < orderBy.size(); i++) {
>   String orderByColumn = orderBy.get(i);
>   String alias = orderByColumn.substring(0,
> orderByColumn.lastIndexOf('
> '));
>
>   if (aliases.containsKey(alias)) {
>     orderByClause.add(orderByColumn);
>   }
>   else {
>     if (orderByColumn.indexOf('.') == -1){
>       throwMalformedColumnNameException("order by",orderByColumn);
>   }
>   ...
>
>
> e-mail me if you can't get it to work and I'll send you my
> source class.
>
> andres



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


RE: Criteria question...

Posted by Andres Quijano - Rizonet <an...@rizo.net>.
now that I recall, I had to patch BasePeer... I think it was (but not
for sure, check old posts)

line 1220

for (int i = 0; i < orderBy.size(); i++) {
  String orderByColumn = orderBy.get(i);
  String alias = orderByColumn.substring(0, orderByColumn.lastIndexOf('
'));

  if (aliases.containsKey(alias)) {
    orderByClause.add(orderByColumn);
  }
  else {
    if (orderByColumn.indexOf('.') == -1){
      throwMalformedColumnNameException("order by",orderByColumn);
  }
  ...


e-mail me if you can't get it to work and I'll send you my source class.

andres

-----Original Message-----
From: William Wong [mailto:william.wong@openwave.com] 
Sent: Martes, 20 de Enero de 2004 01:26 p.m.
To: Apache Torque Users List
Subject: RE: Criteria question...


Hi Andres,

Which version of Torque are u using? I'm currently running 3.1 and got
below exception when I use your solution:

org.apache.torque.TorqueException: Malformed column name in Criteria
order
by: 'RAND() ASC' is not of the form 'table.column'
	at
org.apache.torque.util.BasePeer.throwMalformedColumnNameException(BasePe
er.j
ava:2591)
	at
org.apache.torque.util.BasePeer.createQuery(BasePeer.java:1242)
	at
org.apache.torque.util.BasePeer.createQueryString(BasePeer.java:984)
	at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1391)
	at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1368)

The above exception is throwed by below code in BasePeer.java. It is
expecting the orderby to in a column name format.

            for (int i = 0; i < orderBy.size(); i++)
            {
                String orderByColumn = (String) orderBy.get(i);
                if (orderByColumn.indexOf('.') == -1)
                {
                    throwMalformedColumnNameException("order by",
                            orderByColumn);
                }
                String tableName =
                    orderByColumn.substring(0,
orderByColumn.indexOf('.'));

Any recommendation? I understand your sql statement 'select *, rand() as
rnd from table order by rnd asc limit 2' will work but it seems the
BasePeer is enforcing the column name as the order by argument.

Thanks and regards,
William
-----Original Message-----
From: Andres Quijano - Rizonet [mailto:andres@rizo.net]
Sent: Monday, January 19, 2004 8:12 PM
To: 'Apache Torque Users List'
Subject: RE: Criteria question...


I use this to get a random row from a table:

Criteria crit = new Criteria();
crit.addAsColumn("rnd", "rand()");
crit.addAscendingOrderByColumn("rnd");
crit.setLimit(1);
YourTableNamePeer.doSelect(crit);

hope it helps

-----Original Message-----
From: William Wong [mailto:william.wong@openwave.com]
Sent: Domingo, 18 de Enero de 2004 10:32 p.m.
To: torque-user@db.apache.org
Subject: Criteria question...


Hi All,

Is there any easy way to convert below Mysql select statement into a
Criteria statement?

select * from name order by rand() limit 1;

Can I use Criteria.CUSTOM to achieve it?

Thank you in advance.

Regards,
William


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


RE: Criteria question...

Posted by William Wong <wi...@openwave.com>.
Hi Andres,

Which version of Torque are u using? I'm currently running 3.1 and got below
exception when I use your solution:

org.apache.torque.TorqueException: Malformed column name in Criteria order
by: 'RAND() ASC' is not of the form 'table.column'
	at
org.apache.torque.util.BasePeer.throwMalformedColumnNameException(BasePeer.j
ava:2591)
	at org.apache.torque.util.BasePeer.createQuery(BasePeer.java:1242)
	at org.apache.torque.util.BasePeer.createQueryString(BasePeer.java:984)
	at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1391)
	at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1368)

The above exception is throwed by below code in BasePeer.java. It is
expecting the orderby to in a column name format.

            for (int i = 0; i < orderBy.size(); i++)
            {
                String orderByColumn = (String) orderBy.get(i);
                if (orderByColumn.indexOf('.') == -1)
                {
                    throwMalformedColumnNameException("order by",
                            orderByColumn);
                }
                String tableName =
                    orderByColumn.substring(0, orderByColumn.indexOf('.'));

Any recommendation? I understand your sql statement 'select *, rand() as rnd
from table order by rnd asc limit 2' will work but it seems the BasePeer is
enforcing the column name as the order by argument.

Thanks and regards,
William
-----Original Message-----
From: Andres Quijano - Rizonet [mailto:andres@rizo.net]
Sent: Monday, January 19, 2004 8:12 PM
To: 'Apache Torque Users List'
Subject: RE: Criteria question...


I use this to get a random row from a table:

Criteria crit = new Criteria();
crit.addAsColumn("rnd", "rand()");
crit.addAscendingOrderByColumn("rnd");
crit.setLimit(1);
YourTableNamePeer.doSelect(crit);

hope it helps

-----Original Message-----
From: William Wong [mailto:william.wong@openwave.com]
Sent: Domingo, 18 de Enero de 2004 10:32 p.m.
To: torque-user@db.apache.org
Subject: Criteria question...


Hi All,

Is there any easy way to convert below Mysql select statement into a
Criteria statement?

select * from name order by rand() limit 1;

Can I use Criteria.CUSTOM to achieve it?

Thank you in advance.

Regards,
William


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


RE: Criteria question...

Posted by Andres Quijano - Rizonet <an...@rizo.net>.
I use this to get a random row from a table:

Criteria crit = new Criteria();
crit.addAsColumn("rnd", "rand()");
crit.addAscendingOrderByColumn("rnd");
crit.setLimit(1);
YourTableNamePeer.doSelect(crit);

hope it helps

-----Original Message-----
From: William Wong [mailto:william.wong@openwave.com] 
Sent: Domingo, 18 de Enero de 2004 10:32 p.m.
To: torque-user@db.apache.org
Subject: Criteria question...


Hi All,

Is there any easy way to convert below Mysql select statement into a
Criteria statement?

select * from name order by rand() limit 1;

Can I use Criteria.CUSTOM to achieve it?

Thank you in advance.

Regards,
William


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org