You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Eric Polino <er...@campgroundautomation.com> on 2008/05/22 23:23:25 UTC
joins
Given three tables (This is not accurate SQL syntax...but you get the point).
Reservation {
...
siteId int foreign key with Site,
...
}
Site {
...
siteId int primary key,
typeId int foreign key with SiteType,
...
}
SiteType {
...
siteTypeId int primary key,
name varchar(32),
...
}
How can I do the following SQL statement using Expression,
ExpressionFactory, SelectQuery, etc?
select * from Reservation r, Site s, SiteType st where r.siteId =
s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
TIA.
--
Eric Polino
Campground Automated Systems
Re: joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Oops. Sorry about that.
You need "." between each path component:
Expression exp = ExpressionFactory.matchExp(
Reservation.TO_SITE_PROPERTY
+ "." + Site.TO_SITE_TYPE_PROPERTY
+ "." + SiteType.TYPE_NAME_PROPERTY,
type);
On 5/22/08, Eric Polino <er...@campgroundautomation.com> wrote:
> Doesn't work for me. This is a copy of my code. It barks at me with
> a stack trace I'll place below the code.
>
> Expression exp = ExpressionFactory.matchExp(
> Reservation.TO_SITE_PROPERTY
> + Site.TO_SITE_TYPE_PROPERTY
> + SiteType.TYPE_NAME_PROPERTY,
> type);
> SelectQuery sq = new SelectQuery(Reservation.class, exp);
> List<Reservation> rlist = context.performQuery(sq);
>
>
> [#|2008-05-22T17:45:33.673-0400|INFO|sun-appserver9.1|org.apache.cayenne.access.QueryLogger|_ThreadID=16;_ThreadName=httpSSLWorkerThread-8080-0;|***
> error.
> org.apache.cayenne.exp.ExpressionException: [v.3.0M2 Oct 23 2007
> 13:00:44] Can't resolve path component:
> [Reservation.toSitetoSiteTypetypeName].
> at org.apache.cayenne.map.Entity$PathIterator.next(Entity.java:375)
> at org.apache.cayenne.access.trans.QueryAssemblerHelper.appendObjPath(QueryAssemblerHelper.java:90)
> at org.apache.cayenne.access.trans.QualifierTranslator.objectNode(QualifierTranslator.java:338)
> at org.apache.cayenne.exp.Expression.traverse(Expression.java:455)
> at org.apache.cayenne.exp.Expression.traverse(Expression.java:452)
> at org.apache.cayenne.exp.Expression.traverse(Expression.java:432)
> at org.apache.cayenne.access.trans.QualifierTranslator.doTranslation(QualifierTranslator.java:74)
> at org.apache.cayenne.access.trans.SelectTranslator.createSqlString(SelectTranslator.java:123)
> at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:95)
> at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:71)
> at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
> at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:446)
> at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:67)
> at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:418)
> at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:853)
> at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:415)
> at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
> at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:282)
> at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:59)
> at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1323)
> at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1312)
> at com.campgroundautomation.sunrise.hostedservices.sws.datagateway.ReservationDataGateway.retrieveCriteriaReservationCount(ReservationDataGateway.java:265)
> at com.campgroundautomation.sunrise.hostedservices.sws.datagateway.ReservationDataGateway.retrieveAvailabilityByType(ReservationDataGateway.java:387)
> at com.campgroundautomation.sunrise.hostedservices.sws.ReservationService.checkAvailByType(ReservationService.java:266)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>
>
>
>
>
> On Thu, May 22, 2008 at 5:37 PM, Mike Kienenberger <mk...@gmail.com> wrote:
> > Expression qualifier = ExpressionFactory.matchExp(
> > Reservation.SITE_RELATIONSHIP_PROPERTY
> > + Site.SITE_TYPE_RELATIONSHIP_PROPERTY
> > + SiteType.NAME_ATTRIBUTE_PROPERTY,
> > "foo");
> >
> > SelectQuery query = new SelectQuery(Reservation.class, qualifier );
> >
> > On 5/22/08, Eric Polino <er...@campgroundautomation.com> wrote:
> >> On Thu, May 22, 2008 at 5:23 PM, Eric Polino
> >> <er...@campgroundautomation.com> wrote:
> >> > Given three tables (This is not accurate SQL syntax...but you get the point).
> >> >
> >> > Reservation {
> >> > ...
> >> > siteId int foreign key with Site,
> >> > ...
> >> > }
> >> >
> >> > Site {
> >> > ...
> >> > siteId int primary key,
> >> > typeId int foreign key with SiteType,
> >> > ...
> >> > }
> >> >
> >> > SiteType {
> >> > ...
> >> > siteTypeId int primary key,
> >> > name varchar(32),
> >> > ...
> >> > }
> >> >
> >> > How can I do the following SQL statement using Expression,
> >> > ExpressionFactory, SelectQuery, etc?
> >> >
> >> > select * from Reservation r, Site s, SiteType st where r.siteId =
> >> > s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
> >> >
> >>
> >>
> >> I'm actually trying to get Reservation objects, so it's not '*' I'm
> >> selecting but more like r.field1, r.field2,... But Cayenne can take
> >> care of that for me when the time comes I'm sure.
> >>
> >>
> >> > TIA.
> >> >
> >> > --
> >> > Eric Polino
> >> > Campground Automated Systems
> >> >
> >>
> >>
> >>
> >> --
> >> Eric Polino
> >> Campground Automated Systems
> >>
> >
>
>
>
>
> --
>
> Eric Polino
> Campground Automated Systems
>
Re: joins
Posted by Eric Polino <er...@campgroundautomation.com>.
Doesn't work for me. This is a copy of my code. It barks at me with
a stack trace I'll place below the code.
Expression exp = ExpressionFactory.matchExp(
Reservation.TO_SITE_PROPERTY
+ Site.TO_SITE_TYPE_PROPERTY
+ SiteType.TYPE_NAME_PROPERTY,
type);
SelectQuery sq = new SelectQuery(Reservation.class, exp);
List<Reservation> rlist = context.performQuery(sq);
[#|2008-05-22T17:45:33.673-0400|INFO|sun-appserver9.1|org.apache.cayenne.access.QueryLogger|_ThreadID=16;_ThreadName=httpSSLWorkerThread-8080-0;|***
error.
org.apache.cayenne.exp.ExpressionException: [v.3.0M2 Oct 23 2007
13:00:44] Can't resolve path component:
[Reservation.toSitetoSiteTypetypeName].
at org.apache.cayenne.map.Entity$PathIterator.next(Entity.java:375)
at org.apache.cayenne.access.trans.QueryAssemblerHelper.appendObjPath(QueryAssemblerHelper.java:90)
at org.apache.cayenne.access.trans.QualifierTranslator.objectNode(QualifierTranslator.java:338)
at org.apache.cayenne.exp.Expression.traverse(Expression.java:455)
at org.apache.cayenne.exp.Expression.traverse(Expression.java:452)
at org.apache.cayenne.exp.Expression.traverse(Expression.java:432)
at org.apache.cayenne.access.trans.QualifierTranslator.doTranslation(QualifierTranslator.java:74)
at org.apache.cayenne.access.trans.SelectTranslator.createSqlString(SelectTranslator.java:123)
at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:95)
at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:71)
at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:446)
at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:67)
at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:418)
at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:853)
at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:415)
at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:282)
at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:59)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1323)
at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1312)
at com.campgroundautomation.sunrise.hostedservices.sws.datagateway.ReservationDataGateway.retrieveCriteriaReservationCount(ReservationDataGateway.java:265)
at com.campgroundautomation.sunrise.hostedservices.sws.datagateway.ReservationDataGateway.retrieveAvailabilityByType(ReservationDataGateway.java:387)
at com.campgroundautomation.sunrise.hostedservices.sws.ReservationService.checkAvailByType(ReservationService.java:266)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
On Thu, May 22, 2008 at 5:37 PM, Mike Kienenberger <mk...@gmail.com> wrote:
> Expression qualifier = ExpressionFactory.matchExp(
> Reservation.SITE_RELATIONSHIP_PROPERTY
> + Site.SITE_TYPE_RELATIONSHIP_PROPERTY
> + SiteType.NAME_ATTRIBUTE_PROPERTY,
> "foo");
>
> SelectQuery query = new SelectQuery(Reservation.class, qualifier );
>
> On 5/22/08, Eric Polino <er...@campgroundautomation.com> wrote:
>> On Thu, May 22, 2008 at 5:23 PM, Eric Polino
>> <er...@campgroundautomation.com> wrote:
>> > Given three tables (This is not accurate SQL syntax...but you get the point).
>> >
>> > Reservation {
>> > ...
>> > siteId int foreign key with Site,
>> > ...
>> > }
>> >
>> > Site {
>> > ...
>> > siteId int primary key,
>> > typeId int foreign key with SiteType,
>> > ...
>> > }
>> >
>> > SiteType {
>> > ...
>> > siteTypeId int primary key,
>> > name varchar(32),
>> > ...
>> > }
>> >
>> > How can I do the following SQL statement using Expression,
>> > ExpressionFactory, SelectQuery, etc?
>> >
>> > select * from Reservation r, Site s, SiteType st where r.siteId =
>> > s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
>> >
>>
>>
>> I'm actually trying to get Reservation objects, so it's not '*' I'm
>> selecting but more like r.field1, r.field2,... But Cayenne can take
>> care of that for me when the time comes I'm sure.
>>
>>
>> > TIA.
>> >
>> > --
>> > Eric Polino
>> > Campground Automated Systems
>> >
>>
>>
>>
>> --
>> Eric Polino
>> Campground Automated Systems
>>
>
--
Eric Polino
Campground Automated Systems
Re: joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Expression qualifier = ExpressionFactory.matchExp(
Reservation.SITE_RELATIONSHIP_PROPERTY
+ Site.SITE_TYPE_RELATIONSHIP_PROPERTY
+ SiteType.NAME_ATTRIBUTE_PROPERTY,
"foo");
SelectQuery query = new SelectQuery(Reservation.class, qualifier );
On 5/22/08, Eric Polino <er...@campgroundautomation.com> wrote:
> On Thu, May 22, 2008 at 5:23 PM, Eric Polino
> <er...@campgroundautomation.com> wrote:
> > Given three tables (This is not accurate SQL syntax...but you get the point).
> >
> > Reservation {
> > ...
> > siteId int foreign key with Site,
> > ...
> > }
> >
> > Site {
> > ...
> > siteId int primary key,
> > typeId int foreign key with SiteType,
> > ...
> > }
> >
> > SiteType {
> > ...
> > siteTypeId int primary key,
> > name varchar(32),
> > ...
> > }
> >
> > How can I do the following SQL statement using Expression,
> > ExpressionFactory, SelectQuery, etc?
> >
> > select * from Reservation r, Site s, SiteType st where r.siteId =
> > s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
> >
>
>
> I'm actually trying to get Reservation objects, so it's not '*' I'm
> selecting but more like r.field1, r.field2,... But Cayenne can take
> care of that for me when the time comes I'm sure.
>
>
> > TIA.
> >
> > --
> > Eric Polino
> > Campground Automated Systems
> >
>
>
>
> --
> Eric Polino
> Campground Automated Systems
>
Re: joins
Posted by Eric Polino <er...@campgroundautomation.com>.
On Thu, May 22, 2008 at 5:23 PM, Eric Polino
<er...@campgroundautomation.com> wrote:
> Given three tables (This is not accurate SQL syntax...but you get the point).
>
> Reservation {
> ...
> siteId int foreign key with Site,
> ...
> }
>
> Site {
> ...
> siteId int primary key,
> typeId int foreign key with SiteType,
> ...
> }
>
> SiteType {
> ...
> siteTypeId int primary key,
> name varchar(32),
> ...
> }
>
> How can I do the following SQL statement using Expression,
> ExpressionFactory, SelectQuery, etc?
>
> select * from Reservation r, Site s, SiteType st where r.siteId =
> s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
>
I'm actually trying to get Reservation objects, so it's not '*' I'm
selecting but more like r.field1, r.field2,... But Cayenne can take
care of that for me when the time comes I'm sure.
> TIA.
>
> --
> Eric Polino
> Campground Automated Systems
>
--
Eric Polino
Campground Automated Systems
Re: joins
Posted by Eric Polino <er...@campgroundautomation.com>.
Yes I've done a fair amount of SQLTemplate stuff. The problem there
is that we are supporting both Derby and Postgres and they don't use
completely portable SQL syntax.
On Thu, May 22, 2008 at 6:00 PM, Hans Poo <ha...@welinux.cl> wrote:
> Eric,
>
> Have you tried with SQLTemplate ? i've found it extermely useful, in particular when expressions
> aren't enough (or my knowledge of them is not enough).
>
> This example is for running code and a join with two tables: familia and visita. Returns a list
> of familias.
>
> public List getPersonas(int ano) {
>
> String sql = "select f.* from persona f, visita v "
> + " where f.id_comuna = #bind($id_comuna) "
> + " and v.id_persona = f.id_persona"
> + " and v.id_formulario = 1 "
> + " and extract(year from v.fec_visita) = #bind($ano)";
>
> Map parameters = new HashMap();
> parameters.put("id_comuna", this.getId());
> parameters.put("ano", ano);
>
> SQLTemplate query = new SQLTemplate(Persona.class, sql);
> query.setParameters(parameters);
> System.err.println(query.toString());
>
> return DataContext.getThreadDataContext().performQuery(query);
>
> }
>
> Good luck
> Hans
>
> On Thu, 22 May 2008 17:23:25 -0400, Eric Polino wrote
>> Given three tables (This is not accurate SQL syntax...but you get the point).
>>
>> Reservation {
>> ...
>> siteId int foreign key with Site,
>> ...
>> }
>>
>> Site {
>> ...
>> siteId int primary key,
>> typeId int foreign key with SiteType,
>> ...
>> }
>>
>> SiteType {
>> ...
>> siteTypeId int primary key,
>> name varchar(32),
>> ...
>> }
>>
>> How can I do the following SQL statement using Expression,
>> ExpressionFactory, SelectQuery, etc?
>>
>> select * from Reservation r, Site s, SiteType st where r.siteId =
>> s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
>>
>> TIA.
>>
>> --
>> Eric Polino
>> Campground Automated Systems
>
>
> --
> Open WebMail Project (http://openwebmail.org)
>
>
--
Eric Polino
Campground Automated Systems
Re: joins
Posted by Hans Poo <ha...@welinux.cl>.
Eric,
Have you tried with SQLTemplate ? i've found it extermely useful, in particular when expressions
aren't enough (or my knowledge of them is not enough).
This example is for running code and a join with two tables: familia and visita. Returns a list
of familias.
public List getPersonas(int ano) {
String sql = "select f.* from persona f, visita v "
+ " where f.id_comuna = #bind($id_comuna) "
+ " and v.id_persona = f.id_persona"
+ " and v.id_formulario = 1 "
+ " and extract(year from v.fec_visita) = #bind($ano)";
Map parameters = new HashMap();
parameters.put("id_comuna", this.getId());
parameters.put("ano", ano);
SQLTemplate query = new SQLTemplate(Persona.class, sql);
query.setParameters(parameters);
System.err.println(query.toString());
return DataContext.getThreadDataContext().performQuery(query);
}
Good luck
Hans
On Thu, 22 May 2008 17:23:25 -0400, Eric Polino wrote
> Given three tables (This is not accurate SQL syntax...but you get the point).
>
> Reservation {
> ...
> siteId int foreign key with Site,
> ...
> }
>
> Site {
> ...
> siteId int primary key,
> typeId int foreign key with SiteType,
> ...
> }
>
> SiteType {
> ...
> siteTypeId int primary key,
> name varchar(32),
> ...
> }
>
> How can I do the following SQL statement using Expression,
> ExpressionFactory, SelectQuery, etc?
>
> select * from Reservation r, Site s, SiteType st where r.siteId =
> s.siteId and s.typeId = st.siteTypeId and st.name = 'foo'
>
> TIA.
>
> --
> Eric Polino
> Campground Automated Systems
--
Open WebMail Project (http://openwebmail.org)