Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of blacknext@gmail.com
 designates 64.233.184.232 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=grQ6bws0dTyyRL3G3/btMkLbOchgheLHwNP6WmsyZvaU7Mqewcw/WZVbLbQcAyW2zeFUzRX3wCOnLk/hiYXDWuHEnNwE9IWkUzwnstBySd1ESINFSjDmweFOHnpsBcZb382p08uLS8Z47Nlg06kroKUzZupFp4wSuTSWnf+zAwY=
Message-ID: <f65b8ae30802080635g43942da9le2eb781fbe6d9dcb@mail.gmail.com>
Date: Fri, 8 Feb 2008 09:35:38 -0500
From: "Michael Gentry" <blacknext@gmail.com>
To: user@cayenne.apache.org
Subject: Re: Pb implement JOIN function
In-Reply-To: <47AB7BBC.5030307@smaeur.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <47AB7BBC.5030307@smaeur.com>

Using Cayenne Modeler, you can define a relationship from SMASTER to
JSTATMAP using two joins.  Create the join, say in SMASTER, and click
the "I" inspector button to establish this.  Will this do what you
want?  If so, then you should be able to perform your query something
like this:

Expression expression = Expression.fromString("jstatmap.filtercat =
'CANCELLED'");
SelectQuery  = new SelectQuery(Smaster.class, expression);
List values = dataContext.performQuery(query);


If this isn't what you are looking for, look into the SQLTemplate feature.

/dev/mrg


On Feb 7, 2008 4:44 PM, Francois Eyl <feyl@smaeur.com> wrote:
> Hello guys,
>
> I have a question about how to join multiple object entities.
>
> Concrete sample :
>
> I have 2 tables with no foreign key between them (poor database).
>
> The first one is named SMASTER with 5 columns [SKDDATE (PK), SKDID (PK),
> JOBNAME (PK), JOBSTATUS, STSTATUS].
> The second one is named JSTATMAP with 3 columns, [JOBSTATUS (PK),
> STSTATUS (PK), FILTERCAT].
>
> I also have my cayenne user object entities named Smaster and Jstatmap
> with all their properties and their getters and setters.
>
> I'm trying to create a cayenne query to retrieve Smaster objects related
> to a specifique FILTERCAT value. In pure SQL the request would be
> something like that:
>
> SELECT SKDDATE, SKDID, JOBNAME, JOBSTATUS, STSTATUS
> FROM SMASTER
>     INNER JOIN JSTATMAP ON
>         JSTATMAP.JOBSTATUS=SMASTER.JOBSTATUS AND
>         JSTATMAP.STSTATUS=SMASTER.STSTATUS
> WHERE JSTATMAP.FILTERCAT='CANCELLED'
>
> But I can't find the way with cayenne to do the same thing with Query
> and Expressions. Is there a way to do a "JOIN" between two or multiple
> cayenne object entity?
>
> Thanks.
>
> Francois
>