Re: Complicated SQL queries

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