You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Sampath Uppula <SU...@TechMahindra.com> on 2012/10/09 09:59:52 UTC
How to fetch the Distinct rows based on an attribute.
Hi,
I have a table t_user_allocation_details with many columns, one of the column is user_id.
I want to fetch the distinct data based on user_id.
Please suggest a way to achieve this.
I have tried the below method but it's based on all the columns in the table.
* public void setDistinct(boolean distinct)
If set to true (default is false), tells Cayenne that query should fetch distinct rows.
Thanks,
Sampath Uppula|eConvergence,BT-15|Tech Mahindra
------------------------------------------------------------------------------
S1-W-5572, West Wing, Block-1, Hinjewadi-Phase III, Pune 411 057, INDIA
* Office: +91 20 42250000 Extn: 250879 | Cell: +91 9730745043
Email: SU0064323@techmahindra.com<ma...@techmahindra.com>
www.techmahindra.com<http://www.techmahindra.com/>
[cid:image001.jpg@01CDA621.C296D990]
============================================================================================================================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.============================================================================================================================
Re: How to fetch the Distinct rows based on an attribute.
Posted by Andrus Adamchik <an...@objectstyle.org>.
I am going to close the CAY-1730 Jira. Indeed the right place to get help with Cayenne is here on this list (vs. reporting a bug, for which Jira is of course appropriate).
So regarding your specific query:
> SELECT * FROM t_task_assignment_detail t0 where t0.user_id=112 and exists (select 'X' from t_task_transaction_detail t1 where t1.id = t0.task_transaction_id and t1.task_status='INPROGRESS')
So if I understand what your query is doing, an equivalent but much simpler query can be written like this:
SELECT t0.* FROM t_task_assignment_detail t0
JOIN t_task_transaction_detail t1 ON (t1.id = t0.task_transaction_id)
WHERE t0.user_id=112 and t1.task_status='INPROGRESS'
(Hope I am not missing anything… t_task_assignment_detail to t_task_transaction_detail is N..1)
In terms of Cayenne this is a very simple SelectQuery:
SelectQuery q = new SelectQuery(TaskTransactionDetail.class);
// that is if userId is a class property; if it is an FK to user,
// this part can be done differently
q.andQualifier(ExpressionFactory.matchExp("userId", 112));
q.andQualifier( ExpressionFactory.matchExp("transactionDetail.taskStatus", "INPROGRESS"));
This should do it. Make sure expression property names match your model of course.
HTH,
Andrus
On Oct 9, 2012, at 1:16 PM, "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de> wrote:
> Can't help with your distinctness problem since I do not understand what you're trying to achieve.
>
> Don't know enough about Cayenne to solve your query problem, but that question definitely belongs into this mailing list, where you're talking to people who're using Cayenne.
> In JIRA, you're talking to people who're making Cayenne.
> Your chances are much better with the Cayenne users.
>
> -----Original Message-----
> From: Sampath Uppula [mailto:SU0064323@TechMahindra.com]
> Sent: Tuesday, October 09, 2012 11:57 AM
> To: user@cayenne.apache.org
> Subject: RE: How to fetch the Distinct rows based on an attribute.
>
> Hi,
> Thanks for the response.
> But this is just an example.
> I have many scenarios, where I have to fetch the distinct rows based on a field (attribute).
>
> Can you also please answer the issue# CAY-1730 (https://issues.apache.org/jira/browse/CAY-1730)?
>
> Thanks,
> Sampath Uppula
>
>
> -----Original Message-----
> From: Durchholz, Joachim [mailto:Joachim.Durchholz@hennig-fahrzeugteile.de]
> Sent: Tuesday, October 09, 2012 3:06 PM
> To: user@cayenne.apache.org
> Subject: RE: How to fetch the Distinct rows based on an attribute.
>
> What you want to do does not make sense.
> If there are rows that have the same user_id but different fields elsewhere, which of these rows should be returned?
>
> ________________________________
>
> From: Sampath Uppula [mailto:SU0064323@TechMahindra.com]
> Sent: Tuesday, October 09, 2012 10:00 AM
> To: user@cayenne.apache.org
> Subject: How to fetch the Distinct rows based on an attribute.
>
>
>
> Hi,
>
> I have a table t_user_allocation_details with many columns, one of the column is user_id.
>
> I want to fetch the distinct data based on user_id.
>
> Please suggest a way to achieve this.
>
>
>
> I have tried the below method but it’s based on all the columns in the table.
>
>
>
> * public void setDistinct(boolean distinct)
> If set to true (default is false), tells Cayenne that query should fetch distinct rows.
>
>
>
> Thanks,
>
> Sampath Uppula|eConvergence,BT-15|Tech Mahindra
>
> ------------------------------------------------------------------------------
>
> S1-W-5572, West Wing, Block-1, Hinjewadi-Phase III, Pune 411 057, INDIA
>
> ( Office: +91 20 42250000 Extn: 250879 | Cell: +91 9730745043
>
> Email: SU0064323@techmahindra.com <ma...@techmahindra.com>
>
> www.techmahindra.com <http://www.techmahindra.com/>
>
>
>
> eConvergence
>
>
>
> ============================================================================================================================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 http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/tim/disclaimer.html 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.============================================================================================================================
RE: How to fetch the Distinct rows based on an attribute.
Posted by "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>.
Can't help with your distinctness problem since I do not understand what you're trying to achieve.
Don't know enough about Cayenne to solve your query problem, but that question definitely belongs into this mailing list, where you're talking to people who're using Cayenne.
In JIRA, you're talking to people who're making Cayenne.
Your chances are much better with the Cayenne users.
-----Original Message-----
From: Sampath Uppula [mailto:SU0064323@TechMahindra.com]
Sent: Tuesday, October 09, 2012 11:57 AM
To: user@cayenne.apache.org
Subject: RE: How to fetch the Distinct rows based on an attribute.
Hi,
Thanks for the response.
But this is just an example.
I have many scenarios, where I have to fetch the distinct rows based on a field (attribute).
Can you also please answer the issue# CAY-1730 (https://issues.apache.org/jira/browse/CAY-1730)?
Thanks,
Sampath Uppula
-----Original Message-----
From: Durchholz, Joachim [mailto:Joachim.Durchholz@hennig-fahrzeugteile.de]
Sent: Tuesday, October 09, 2012 3:06 PM
To: user@cayenne.apache.org
Subject: RE: How to fetch the Distinct rows based on an attribute.
What you want to do does not make sense.
If there are rows that have the same user_id but different fields elsewhere, which of these rows should be returned?
________________________________
From: Sampath Uppula [mailto:SU0064323@TechMahindra.com]
Sent: Tuesday, October 09, 2012 10:00 AM
To: user@cayenne.apache.org
Subject: How to fetch the Distinct rows based on an attribute.
Hi,
I have a table t_user_allocation_details with many columns, one of the column is user_id.
I want to fetch the distinct data based on user_id.
Please suggest a way to achieve this.
I have tried the below method but it’s based on all the columns in the table.
* public void setDistinct(boolean distinct)
If set to true (default is false), tells Cayenne that query should fetch distinct rows.
Thanks,
Sampath Uppula|eConvergence,BT-15|Tech Mahindra
------------------------------------------------------------------------------
S1-W-5572, West Wing, Block-1, Hinjewadi-Phase III, Pune 411 057, INDIA
( Office: +91 20 42250000 Extn: 250879 | Cell: +91 9730745043
Email: SU0064323@techmahindra.com <ma...@techmahindra.com>
www.techmahindra.com <http://www.techmahindra.com/>
eConvergence
============================================================================================================================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 http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/tim/disclaimer.html 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.============================================================================================================================
RE: How to fetch the Distinct rows based on an attribute.
Posted by Sampath Uppula <SU...@TechMahindra.com>.
Hi,
Thanks for the response.
But this is just an example.
I have many scenarios, where I have to fetch the distinct rows based on a field (attribute).
Can you also please answer the issue# CAY-1730 (https://issues.apache.org/jira/browse/CAY-1730)?
Thanks,
Sampath Uppula
-----Original Message-----
From: Durchholz, Joachim [mailto:Joachim.Durchholz@hennig-fahrzeugteile.de]
Sent: Tuesday, October 09, 2012 3:06 PM
To: user@cayenne.apache.org
Subject: RE: How to fetch the Distinct rows based on an attribute.
What you want to do does not make sense.
If there are rows that have the same user_id but different fields elsewhere, which of these rows should be returned?
________________________________
From: Sampath Uppula [mailto:SU0064323@TechMahindra.com]
Sent: Tuesday, October 09, 2012 10:00 AM
To: user@cayenne.apache.org
Subject: How to fetch the Distinct rows based on an attribute.
Hi,
I have a table t_user_allocation_details with many columns, one of the column is user_id.
I want to fetch the distinct data based on user_id.
Please suggest a way to achieve this.
I have tried the below method but it’s based on all the columns in the table.
* public void setDistinct(boolean distinct)
If set to true (default is false), tells Cayenne that query should fetch distinct rows.
Thanks,
Sampath Uppula|eConvergence,BT-15|Tech Mahindra
------------------------------------------------------------------------------
S1-W-5572, West Wing, Block-1, Hinjewadi-Phase III, Pune 411 057, INDIA
( Office: +91 20 42250000 Extn: 250879 | Cell: +91 9730745043
Email: SU0064323@techmahindra.com <ma...@techmahindra.com>
www.techmahindra.com <http://www.techmahindra.com/>
eConvergence
============================================================================================================================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 http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/tim/disclaimer.html 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.============================================================================================================================
Re: Problem - fetching the Distinct rows based on a Property
Posted by Andrus Adamchik <an...@objectstyle.org>.
> -- 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 <SU...@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 <SU...@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.============================================================================================================================
>
RE: Problem - fetching the Distinct rows based on a Property
Posted by Sampath Uppula <SU...@TechMahindra.com>.
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 <SU...@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.============================================================================================================================
Re: Problem - fetching the Distinct rows based on a Property
Posted by Felipe Martín Santos <ga...@gmail.com>.
// 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 <SU...@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.============================================================================================================================
Problem - fetching the Distinct rows based on a Property
Posted by Sampath Uppula <SU...@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.============================================================================================================================
RE: How to fetch the Distinct rows based on an attribute.
Posted by "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>.
What you want to do does not make sense.
If there are rows that have the same user_id but different fields elsewhere, which of these rows should be returned?
________________________________
From: Sampath Uppula [mailto:SU0064323@TechMahindra.com]
Sent: Tuesday, October 09, 2012 10:00 AM
To: user@cayenne.apache.org
Subject: How to fetch the Distinct rows based on an attribute.
Hi,
I have a table t_user_allocation_details with many columns, one of the column is user_id.
I want to fetch the distinct data based on user_id.
Please suggest a way to achieve this.
I have tried the below method but it’s based on all the columns in the table.
* public void setDistinct(boolean distinct)
If set to true (default is false), tells Cayenne that query should fetch distinct rows.
Thanks,
Sampath Uppula|eConvergence,BT-15|Tech Mahindra
------------------------------------------------------------------------------
S1-W-5572, West Wing, Block-1, Hinjewadi-Phase III, Pune 411 057, INDIA
( Office: +91 20 42250000 Extn: 250879 | Cell: +91 9730745043
Email: SU0064323@techmahindra.com <ma...@techmahindra.com>
www.techmahindra.com <http://www.techmahindra.com/>
eConvergence
============================================================================================================================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 http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/tim/disclaimer.html internally within Tech Mahindra.============================================================================================================================