cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Problem - fetching the Distinct rows based on a Property
Date Tue, 30 Oct 2012 15:03:14 GMT

> -- why don't we provide a way to restrict the columns while fetching the data?
> Ex: object having set of columns from which want fetch the data for only few columns...using
the object entity.

ORM (Cayenne including) is based on the idea of object identity. In other words each persistent
Java object should have one and only one DB row. DB rows are identified by unique PK of course.
So based on that statement you can't fetch a list of objects for your query that essentially
aggregates multiple rows. 

Having said that, EJBQLQuery ( http://cayenne.apache.org/doc/ejbqlquery.html ) does support
scalar results, so you can still kind of do it, expressing your columns as properties of the
ObjEntity:

SELECT DISTINCT e.storyId FROM QCExtract e WHERE executionStatus in ('PASSED','BLOCKED')

This will return a List<Integer>. You will still have to build the EJBQL query as String
(we'll be adding API-based assembly in the upcoming releases), but at least this is not SQL.
All the names are Java class properties, so you can use generated constants from your java
classes, etc.

Andrus


On Oct 30, 2012, at 2:37 PM, Sampath Uppula <SU0064323@TechMahindra.com> wrote:

> Thanks for the solution.
> But I don't want to write the sql query in the java code.
> 
> Is there any way to achieve this?
> 
> -- why don't we provide a way to restrict the columns while fetching the data?
> Ex: object having set of columns from which want fetch the data for only few columns...using
the object entity.
> 
> Thanks,
> Sampath Uppula
> 
> 
> -----Original Message-----
> From: Felipe Martín Santos [mailto:gaditaman@gmail.com] 
> Sent: Tuesday, October 30, 2012 4:40 PM
> To: user@cayenne.apache.org
> Subject: Re: Problem - fetching the Distinct rows based on a Property
> 
> // Extract is the obj-entity you have for the T_QC_EXTRACT db-entity
> SQLTemplate query = new SQLTemplate(Extract.class, "SELECT DISTINCT
> STORY_ID FROM T_QC_EXTRACT WHERE EXECUTION_STATUS IN ('PASSED','BLOCKED')");
> 
> SQLResult resultDescriptor = new SQLResult();
> resultDescriptor.addColumnResult("STORY_ID");
> query.setResult(resultDescriptor);
> 
> List<DataObject> dataObjects = context.performQuery(query);
> 
> 
> Is this what you need??
> 
> You can see more here:
> http://cayenne.apache.org/doc/sqltemplate-result-mapping.html
> 
> 
> 2012/10/30 Sampath Uppula <SU0064323@techmahindra.com>
> 
>> Hi,
>> 
>> I have  a table T_QC_EXTRACT with many columns, few of them are STORY_ID,
>> EXECUTION_STATUS.
>> I want to fetch the distinct STORY_ID based on EXECUTION_STATUS IN
>> ('PASSED','BLOCKED');
>> 
>> SELECT DISTINCT STORY_ID FROM T_QC_EXTRACT WHERE EXECUTION_STATUS IN
>> ('PASSED','BLOCKED');
>> 
>> Please suggest a way to achieve this using Cayenne.
>> 
>> Thanks,
>> Sampath Uppula
>> 
>> ============================================================================================================================Disclaimer:
>> This message and the information contained herein is proprietary and
>> confidential and subject to the Tech Mahindra policy statement, you may
>> review the policy at <a href="http://www.techmahindra.com/Disclaimer.html
>> ">http://www.techmahindra.com/Disclaimer.html</a> externally and <a href="
>> http://tim.techmahindra.com/tim/disclaimer.html">
>> http://tim.techmahindra.com/tim/disclaimer.html</a> internally within
>> Tech
>> Mahindra.============================================================================================================================
> 
> ============================================================================================================================Disclaimer:
 This message and the information contained herein is proprietary and confidential and subject
to the Tech Mahindra policy statement, you may review the policy at <a href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a>
externally and <a href="http://tim.techmahindra.com/tim/disclaimer.html">http://tim.techmahindra.com/tim/disclaimer.html</a>
internally within Tech Mahindra.============================================================================================================================
> 


Mime
View raw message