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/
