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:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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" >> <[EMAIL PROTECTED]> >> 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" <[EMAIL PROTECTED]> >>>> 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 > > > >
