Re: Complicated SQL queries

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  
<[email protected]> 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 <[email protected]>  
> 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: [email protected]
>> 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 <[email protected]>
>> 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
>> > <[email protected]> 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 <[email protected]>
>> > > 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/