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 > > > >
