You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cayenne.apache.org by Andreas Hartmann <an...@apache.org> on 2010/01/20 17:10:43 UTC

Create count(*) query based on SelectQuery

Hi everyone,

I'm mainly using SelectQuery queries, built using quite a complex 
programmatic infrastructure. But now I have the requirement to obtain 
the total number of rows (to set the Content-Range header in a REST 
service).

So basically I have a SelectQuery which does

   SELECT … FROM foo WHERE <whereClause> LIMIT … OFFSET …;

and I need

   SELECT count(*) FROM foo WHERE <whereClause>;

Is there a straightforward way to build such a query based on the 
SelectQuery's expression? I tried Expression.toEJBQL() but faced a 
couple of problems which have already been described on this list (e.g. 
the likeIgnoreCase issue).

Do I really have to build a string-based SQL or EJBQL query from scratch?

Thanks a lot for any hints!

Best regards,
Andreas



-- 
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01

Re: Create count(*) query based on SelectQuery

Posted by Andrey Razumovsky <ra...@gmail.com>.

Here's and (old) version of my CountHelper class that automatically creates
COUNT statements for SelectQuery. I used to do same for EJBQL, but that was
much harder.
http://osdir.com/ml/user-cayenne-apache/2009-09/msg00012.html

2010/1/20 Andreas Hartmann <an...@apache.org>

> Hi everyone,
>
> I'm mainly using SelectQuery queries, built using quite a complex
> programmatic infrastructure. But now I have the requirement to obtain the
> total number of rows (to set the Content-Range header in a REST service).
>
> So basically I have a SelectQuery which does
>
>  SELECT … FROM foo WHERE <whereClause> LIMIT … OFFSET …;
>
> and I need
>
>  SELECT count(*) FROM foo WHERE <whereClause>;
>
> Is there a straightforward way to build such a query based on the
> SelectQuery's expression? I tried Expression.toEJBQL() but faced a couple of
> problems which have already been described on this list (e.g. the
> likeIgnoreCase issue).
>
> Do I really have to build a string-based SQL or EJBQL query from scratch?
>
> Thanks a lot for any hints!
>
> Best regards,
> Andreas
>
>
>
> --
> Andreas Hartmann, CTO
> BeCompany GmbH
> http://www.becompany.ch
> Tel.: +41 (0) 43 818 57 01
>
>


-- 
Andrey

Re: Create count(*) query based on SelectQuery

Posted by Andreas Hartmann <an...@apache.org>.

Hi Andrus,

Am 20.01.10 17:22, schrieb Andrus Adamchik:
> Short of writing custom extensions of SelectQuery, yes, EJBQL or
> SQLTemplate is the answer.
>
> We are designing a combined SelectQuery / EJBQLQuery that can be created
> either via String or using API as a part of 3.1, but in 3.0 this is not
> an option.

OK, thanks for your reply! I'm looking forward to 3.1 then :)

Best regards,
Andreas


>
> Andrus
>
> On Jan 20, 2010, at 6:10 PM, Andreas Hartmann wrote:
>
>> Hi everyone,
>>
>> I'm mainly using SelectQuery queries, built using quite a complex
>> programmatic infrastructure. But now I have the requirement to obtain
>> the total number of rows (to set the Content-Range header in a REST
>> service).
>>
>> So basically I have a SelectQuery which does
>>
>> SELECT … FROM foo WHERE <whereClause> LIMIT … OFFSET …;
>>
>> and I need
>>
>> SELECT count(*) FROM foo WHERE <whereClause>;
>>
>> Is there a straightforward way to build such a query based on the
>> SelectQuery's expression? I tried Expression.toEJBQL() but faced a
>> couple of problems which have already been described on this list
>> (e.g. the likeIgnoreCase issue).
>>
>> Do I really have to build a string-based SQL or EJBQL query from scratch?
>>
>> Thanks a lot for any hints!
>>
>> Best regards,
>> Andreas
>>
>>
>>
>> --
>> Andreas Hartmann, CTO
>> BeCompany GmbH
>> http://www.becompany.ch
>> Tel.: +41 (0) 43 818 57 01
>>
>>
>
>


-- 
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01

Re: Create count(*) query based on SelectQuery

Posted by Andrus Adamchik <an...@objectstyle.org>.

Short of writing custom extensions of SelectQuery, yes, EJBQL or  
SQLTemplate is the answer.

We are designing a combined SelectQuery / EJBQLQuery that can be  
created either via String or using API as a part of 3.1, but in 3.0  
this is not an option.

Andrus

On Jan 20, 2010, at 6:10 PM, Andreas Hartmann wrote:

> Hi everyone,
>
> I'm mainly using SelectQuery queries, built using quite a complex  
> programmatic infrastructure. But now I have the requirement to  
> obtain the total number of rows (to set the Content-Range header in  
> a REST service).
>
> So basically I have a SelectQuery which does
>
>  SELECT … FROM foo WHERE <whereClause> LIMIT … OFFSET …;
>
> and I need
>
>  SELECT count(*) FROM foo WHERE <whereClause>;
>
> Is there a straightforward way to build such a query based on the  
> SelectQuery's expression? I tried Expression.toEJBQL() but faced a  
> couple of problems which have already been described on this list  
> (e.g. the likeIgnoreCase issue).
>
> Do I really have to build a string-based SQL or EJBQL query from  
> scratch?
>
> Thanks a lot for any hints!
>
> Best regards,
> Andreas
>
>
>
> -- 
> Andreas Hartmann, CTO
> BeCompany GmbH
> http://www.becompany.ch
> Tel.: +41 (0) 43 818 57 01
>
>