You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cayenne.apache.org by Francois Eyl <fe...@smaeur.com> on 2008/02/07 22:44:28 UTC

Pb implement JOIN function

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

Re: Pb implement JOIN function

Posted by Francois Eyl <fe...@smaeur.com>.

Hi Michael,

thanks for your reply, that works for me ;)

Michael Gentry wrote:
> 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 <fe...@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
>>
>>     
>
> _________________________________________________________________________
>
> Ce message a été vérifié par l'antivirus de MDaemon (md6).
>
> Par précaution, n'ouvrez pas de pièces jointes de correspondants inconnus.
> _________________________________________________________________________
>
>
> ___________________________________________________
>
> Ce message a été vérifié par l'antivirus de MDaemon 5 .
>
> Par précaution, n'ouvrez pas de pièces jointes de correspondants inconnus.
> ___________________________________________________
>
>
>

Re: Pb implement JOIN function

Posted by Michael Gentry <bl...@gmail.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 <fe...@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
>