cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Schröder <Peter.Schroe...@freenet-ag.de>
Subject AW: how to call a custom query
Date Fri, 02 Feb 2007 07:25:26 GMT
hi frank,

you may use the object-graph to execute the queries you want. there is no joining with cayenne-syntax.
cayenne will translate your query into an sql-statement with any joins that will be nessessary.

so

query += " FROM schedules";

query += " INNER JOIN procedures ON schedules.procedure_id = 
procedures.procedure_id";

query += " INNER JOIN providers ON schedules.provider_id = 
providers.provider_id"; 

may go as something like

"where schedules.procedureId = XYZ and schedules.proviederId = ABC"

cayenne sees the relation and creates a join or what ever it thinks that should be done.



-----Urspr√ľngliche Nachricht-----
Von: Frank [mailto:farocco@hotmail.com] 
Gesendet: Freitag, 2. Februar 2007 04:42
An: user@cayenne.apache.org
Betreff: Re: how to call a custom query

>>I'm not sure why do you want to execute just a SQL. After doing query
like you are proposing no data could be edited and saved back to db
easily.
Cayenne is a ORM, so why not getting a list of schedules ordered by
procedure name:
Expression exp = ExpressionFactory.matchExpr(Schedule.DATE_PROPERTY,
new Date());
<<
I do not know how to join tables in the Modeler, that is why I opted for the 
rawSelect.
If I just use the Schedule.class, there is no procedure_name field, just 
procedure_id

Frank
----- Original Message ----- 
From: "Marcin Skladaniec" <marcin@ish.com.au>
To: <user@cayenne.apache.org>
Sent: Thursday, February 01, 2007 10:09 PM
Subject: Re: how to call a custom query


> Hi
> schedule_date = CURDATE():
> what is the schedule_date data type ? Check if you are not comparing 
> TIMESTAMP with DATE.
>
>
> I would use the functionality of NamedQuery. All you do you define a 
> Query in Modeller, example :
>
> SELECT #result('count(*)' 'int' 'C') FROM $entityName $whereClause
>
> and in the code you do something like :
>
> HashMap map = new HashMap();
> map.put("entityName", entity);
> map.put("whereClause", where);
>
> NamedQuery query = new NamedQuery("SpecialCount", map);
> Map row = (Map) getContext().performQuery(query).get(0);
> return ((Number) row.get("C")).intValue();
>
>
> I'm not sure why do you want to execute just a SQL. After doing query 
> like you are proposing no data could be edited and saved back to db 
> easily.
> Cayenne is a ORM, so why not getting a list of schedules ordered by 
> procedure name:
> Expression exp = ExpressionFactory.matchExpr(Schedule.DATE_PROPERTY,  new 
> Date());
> Ordering o = new Ordering(Schedule.PROCEDURE_PROPERTY 
> +"."+Procedure.NAME_PROPERTY);
> SelectQuery sq = new SelectQuery(Schedule.class, exp);
> sq.addOrdering(o);
>
> List schedules = context.performQuery(sq);
> so you have the schedules
> now just access the values you are want to display.
>
> Marcin
>
>
> On 02/02/2007, at 12:41 PM, Frank wrote:
>
>> Here is my code.
>> My table show no data. I have three records for this query.
>>
>> I am trying to display procedure_name, provider_name, office_phone, 
>> alt_phone
>> The _Schedules class does not have these fields.
>> What do I need to do?
>>
>> Thanks
>> Frank
>> public List getRecords() {
>>
>> String query = "SELECT ALL 
>> schedules.schedule_id,schedules.schedule_date,";
>>
>> query += " procedures.procedure_name,providers.provider_name,";
>>
>> query += " providers.office_phone,providers.alt_phone";
>>
>> query += " FROM schedules";
>>
>> query += " INNER JOIN procedures ON schedules.procedure_id = 
>> procedures.procedure_id";
>>
>> query += " INNER JOIN providers ON schedules.provider_id = 
>> providers.provider_id";
>>
>> query += " WHERE schedule_date = CURDATE()";
>>
>> query += " ORDER BY procedure_name";
>>
>> DataContext context = DataContext.getThreadDataContext();
>>
>> SQLTemplate rawSelect = new SQLTemplate(Schedules.class, query);
>>
>> List records = context.performQuery(rawSelect);
>>
>> return records;
>>
>> }
>>
>> ----- Original Message ----- From: "Christian Mittendorf" 
>> <christian.mittendorf@freenet.de>
>> To: <user@cayenne.apache.org>
>> Sent: Thursday, February 01, 2007 7:16 PM
>> Subject: Re: how to call a custom query
>>
>>
>>> Please check out the documentation at the great new Cayenne homepage:
>>>
>>> http://cayenne.apache.org/doc20/queries.html
>>>
>>> ...especially the part on SQLTemplates
>>>
>>> Christian
>>>
>>> P.S. The link to the API  (http://cayenne.apache.org/1_2/api/ cayenne/ 
>>> org/objectstyle/cayenne/query/package-summary.html) from  the 1.2 
>>> documentation returns a 404 error....
>>>
>>>
>>> Am 02.02.2007 um 01:02 schrieb Frank:
>>>
>>>> Can anyone help me with the a short example on how to do this?
>>>>
>>>> Thanks
>>>>
>>>> Frank
>>>> ----- Original Message ----- From: "Frank" <farocco@hotmail.com>
>>>> To: <user@cayenne.apache.org>
>>>> Sent: Thursday, February 01, 2007 11:33 AM
>>>> Subject: how to call a custom query
>>>>
>>>>
>>>>> Hello,
>>>>>
>>>>> I have the following raw query defined.
>>>>> SELECT BSYDTAA.BSYPEMP.EMHSP#, BSYDTAA.BSYPEMP.EMYLNM, 
>>>>> BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4, 
>>>>> BSYDTAC.BPRPPCP.PCLDDS, BSYDTAA.BSYPEMP.EMEMP# AS EMEMP, 
>>>>> BSYDTAA.BSYPEMP.EMWPH# AS WPHONE FROM BSYDTAA.BSYPEMP, 
>>>>> BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP.EMHSP# =  BSYDTAC.BPRPPCP.PCHSP#

>>>>> AND BSYDTAA.BSYPEMP.EMDEP4 =  BSYDTAC.BPRPPCP.PCDEP4 AND 
>>>>> (BSYDTAA.BSYPEMP.EMHSP# IN (1,5)) AND  (BSYDTAA.BSYPEMP.EMDOT=0)
>>>>> AND EMEMP# = ?
>>>>>
>>>>> How do I call this from code using the SelectQuery and passing  in 
>>>>> the value?
>>>>>
>>>>> Thanks
>>>>>
>>>>> Frank
>>>>>
>>>>
>>>
>>
>
> Marcin
>
>
>
> 


Mime
View raw message