Re: Complicated SQL queries

[Jurgen Doll]

If we were getting DataRow(s) returned then yes, but with a column[] no,  
and as far as I know there's no way to restructure the ObjectSelect query  
to return suitable DataRow(s).

Alternatively there is a way to do the transformation as part of the query  
with:

List< OBJECT_E > results = ObjectSelect.query( ObjEntityA.class )
.columns( .... )
.where( .... )
.map( col -> createObjectE( col ) )
.select( context );



On Wed, 31 Jan 2024 18:26:46 +0200, Christian Gonzalez  
<christian.gonza...@smartscrubs.com> wrote:

Unfortunately the relationship between ObjectEntityE and ObjectEntityA is
only one way and it's going from A to E not the other way around which  
was
one of the reasons why the query was originally done using raw SQL
statements. Is there a way of manually doing what I'm imagining object
select does in the background when we specify ObjEntity.class in the  
query?


On Wed, Jan 31, 2024 at 12:21 AM Jurgen Doll <jur...@ivoryemr.co.za>  
wrote:

Hi Christian

Since the selected columns are all that’s required for ObjEntityE and
there’s a simple relationship between ObjEntityE and ObjEntityA we  
should
be able to just get ObjEntityE directly:

List<ObjEntityE> results = ObjectSelect.query( ObjEntityE.class )
.where( ObjEntityE.PROP_FOR_OBJ_A
     .dot( ObjEntityA.PROP_FOR_OBJ_B )
     .dot( ObjEntityB.PROP_FOR_OBJ_C )
     .dot( ObjEntityC.PROP_FOR_OBJ_D )
     .dot( ObjEntityD.PROP_B )
     .eq( "somevalue" ) )
.select( context );

Regards
Jurgen


From: Christian Gonzalez
Sent: Tuesday, 30 January 2024 18:22
To: user@cayenne.apache.org
Subject: Re: Complicated SQL queries

Sorry for the late follow up, but this query did return the rows I  
wanted
using purely cayenne expressions and object select rather than creating  
a
new objEntity in the model. My last question is if there is any fast  
way to
convert the returned Array Objects into the ObjEntity class for
ObjectEntityA. The three rows should be the only properties in the  
class so
I was wondering if there was a faster way to automatically convert them
rather than having to create a new Object using objectContext.newObject  
and
manually setting the values.

On Mon, Jan 22, 2024 at 12:19 AM Jurgen Doll <jur...@ivoryemr.co.za>
wrote:

> Ahh, my bad again - I missed the whole point of using ObjectSelect
> somehow, probably because it's difficult to explain without having  
your
> model. Anyways here's a shot at it, conceptually the same as what you  
did
> in the modeller:
>
> List<Object[]> results = ObjectSelect.query( ObjEntityA.class )
> .columns( ObjEntityA.PROP_FOR_OBJ_E.dot( ObjEntityE.PROP_A ),
>                     ObjEntityA.PROP_FOR_OBJ_E.dot( ObjEntityE.PROP_B  
),
>                     ObjEntityA.PROP_FOR_OBJ_E.dot( ObjEntityE.PROP_C  
) )
> .where(  ObjEntityA.PROP_FOR_OBJ_B.dot( ObjEntityB.PROP_FOR_OBJ_C  
).dot(
> ObjEntityC.PROP_FOR_OBJ_D )
>                   .dot( ObjEntityD.PROP_B )  // this is t3.varB
>                   .eq( "somevalue" ) )
> .select( context );
>
>
>
> On Fri, 19 Jan 2024 22:45:24 +0200, Christian Gonzalez
> <christian.gonza...@smartscrubs.com> wrote:
>
> > Looks like it worked, thank you. It is definitely an interesting  
way of
> > doing it although I'm not sure about how I feel about having to  
make a
> > new
> > entity every time a query like this is used but hopefully it won't  
be
too
> > often.
> >
> > Thank you for your help.
> >
> >
> > On Wed, Jan 17, 2024 at 11:08 AM Jurgen Doll <jur...@ivoryemr.co.za>
> > wrote:
> >
> >> Ahh, sorry my bad I didn't see the t0 for tableE.
> >>
> >> There may be other ways to do it but I think adding t3.varB as an
> >> attribute to the object entity might be the simplest.
> >>
> >> Regards
> >> Jurgen
>




--
Using Opera's mail client: http://www.opera.com/mail/