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:farocc..otmail.com]
Gesendet: Freitag, 2. Februar 2007 04:42
An: use..ayenne.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" <marci..sh.com.au>
To: <use..ayenne.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.mittendor..reenet.de>
>> To: <use..ayenne.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" <farocc..otmail.com>
>>>> To: <use..ayenne.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
>
>
>
>
This archive was generated by hypermail 2.0.0 : Fri Feb 02 2007 - 02:26:05 EST