Hi Jurgen, I'm a little confused about step 8. I have created the attributes I want to fetch using the db.attribute path from tableA->tableE. However in my query the columns I want aren't fetched traversing all the way from tableA->tableB->....tableE but rather just from tableA immediately to tableE since a relationship exists in the dbEntity. The traversal from tableA all the way to tableD is to be able to use a column in tableD as a qualifier(the WHERE statement). Would that qualifier be done through just an objEntity Relationship then?
On Wed, Jan 17, 2024 at 12:05 AM Jurgen Doll <jur...@ivoryemr.co.za> wrote: > Hi Christian > > Try the following steps: > > 1. Open Cayenne Modeler > 2. Create an ObjEntity > 3. Give it a name and select tableA > 4. Click on the Properties tab > 5. Click on "Create Attribute" icon/button > 6. Click on "Edit Attribute" icon/button > 7. Enter the Attributes Name and select its Java Type > 8. Click on the relationship from tableA -> tableB, and then from > tableB > -> tableC, .... until tableE and then select the column > 9. Click on the "Select path" button (above the relationship lists) > 10. Click on "Done" (bottom right) and repeat for next attribute > > Regards > Jurgen > > > On Tue, 16 Jan 2024 17:58:23 +0200, Christian Gonzalez > <christian.gonza...@smartscrubs.com> wrote: > > > 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 > >> > > > -- > Using Opera's mail client: http://www.opera.com/mail/ >
