Yes, First I would like to amend some mistakes I made on the original query.
The select columns should be from t4 not t3, and the where clause should be from t3 not t4. Also I decided to change some names of the variables/columns where the joins happen since they are not all joined through the same column. The corrected query should look like this SELECT t4.columnA, t4. columnB, t4.columnC FROM catalog.dbo.tableA t0 JOIN catalog.dbo.tableB t1 on t0.varA = t1.varA JOIN catalog.dbo.tableC t2 on t1.varA = t2.varA, JOIN catalog.dbo.tableD t3 on t2.varB = t3.varB JOIN catalog.dbo.tableE t4 on t0.varC = t4.varC and 4 on t0.varD = t4.varD, WHERE t3.varB = 'somevalue' For this query the model uses 5 different tables / obj entities. t0 our starting table has two relevant relationships with t1 and t4 (our desired table). The relationship with t1 is done through one column/attribute and has a reverse relationship as well. The relationship with t4 however is done through two attributes/columns but it does not have a reverse relationship so we cannot perform a query starting from t4 going to t0. As pointed out t1 has a relationship with t0 and also a relationship with t2 on one column/attribute, this one has a reverse relationship as well and that one is 'To Many' and 'To Dep PK'. t2 has the mentioned relationship with t1 and also a relationship with t3 on one column/attribute and is 'To Many' and 'To Dep PK' as well as has its respective reverse relationship. t3 has its relationship with t2 on a single variable/attribute and then we use t3 to evaluate. Please let me know if there is any additional info you might need. Thank you, Christian Gonzalez On Sun, Jan 14, 2024 at 12:33 PM Andrew Lindesay <a...@lindesay.co.nz> wrote: > Hello Christian; > > Are you able to outline how you have the tables modeled in Cayenne as your > question relies on that modelling. > > Regards. > > -- > Andrew Lindesay > > On Sat, 13 Jan 2024, at 10:59, Christian Gonzalez wrote: > > Hi, I was wondering if there was a way to perform queries where the > ending > > table/class is different from the starting one. I have a query where the > > result columns (the SELECT part) I want are from a different table than > the > > starting one. It looks something like this: > > > > SELECT t3.columnA, t3. columnB, t3. columnC > > FROM catalog.dbo.tableA t0 > > JOIN catalog.dbo.tableB t1 on t0.varA = t1.varA > > JOIN catalog.dbo.tableC t2 on t1.varA = t2.varA, > > JOIN catalog.dbo.tableD t3 on t2.varA = t3.varA > > JOIN catalog.dbo.tableE t4 on t0.varA = t4.varA, > > WHERE t4.varB = 'somevalue' > > > > The relationships are only one way, going from tableA to tableC the > reason > > being that the query is more efficient that way. > > > > The query is currently being done through a raw SQL string using > > SQLTemplate but we are trying to update it to avoid having raw SQL > > statements and using the cayenne ObjectSelect and Expressions instead. > > > > If not, is there a way to expose the PathTranslator classes to have a > > little more control over how the path expressions are converted to SQL > > statements? > > > > The project currently uses cayenne 4.2.M3 > > > > Thank you, > > Christian >
