You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Markus Reich <ma...@markusreich.at> on 2013/04/23 06:49:03 UTC
Relationship
Hi,
with the wonderful Cayenne Modeler it's possible to easily create relations
between DB entities.
In my case I have a central table for comments, with a key specifying the
content.
No I need to map this key field to a constant literal, as I know it is just
possible to join fields with fields of the target entity :-(
Is there a workaround (I don't like to make a lot of views an join them ;-))
regards
Meex
Re: Relationship
Posted by Markus Reich <ma...@markusreich.at>.
this seems to be a nice idea using a provider :-)
2013/4/23 Mike Kienenberger <mk...@gmail.com>
> Long ago, in Cayenne 1.1, I did something similar, although I don't
> know if it's now the best way to do it, or if this will completely
> solve your problem. I had defined a separate relationship using the
> same db field for every object I wanted to relate to my entity.
>
> I had a ChangeLog entity, similar to your Comments entity, and I
> needed a relationship between it and every other existing entity.
> Because I only needed to set the foreign key,
>
> aChangeLogObject.setForeign<ENTITY>Object(new<ENTITY>);
>
> I did not create the reverse changeLogList object relationships
> because I did not need them.
> In the model, it looks like this. Note that I had to create a new
> relationship for each object:
>
> ==================
> <obj-entity name="ChangeLog"
> className="com.xyz.cayenne.ebpp.entity.ChangeLog"
> lock-type="optimistic" dbEntityName="LOG">
> <obj-attribute name="foreignRecordKey"
> type="java.lang.Integer" lock="true"
> db-attribute-path="FOREIGN_RECORD_KEY"/>
> <obj-attribute name="tableName" type="java.lang.String"
> lock="true" db-attribute-path="TABLE_NAME"/>
>
> [...]
>
>
> <obj-relationship name="foreignAccount" source="ChangeLog"
> target="Account" db-relationship-path="foreignAccount"/>
> <obj-relationship name="foreignAddress" source="ChangeLog"
> target="Address" db-relationship-path="foreignAddress"/>
>
> [...]
>
>
> <db-relationship name="foreignAccount" source="LOG"
> target="ACCOUNT" toMany="false">
> <db-attribute-pair source="FOREIGN_RECORD_KEY"
> target="ACCOUNT_NUMBER"/>
> </db-relationship>
> <db-relationship name="foreignAddress" source="LOG"
> target="ADDRESS" toMany="false">
> <db-attribute-pair source="FOREIGN_RECORD_KEY" target="ID"/>
> </db-relationship>
> ==================
>
> In my latest work project, comments were set up differently. There
> is no direct relationship between comments and other objects, either
> in the database or in the code.
>
> Instead, we have a CommentListProvider class which looks up all
> comments attached to an object by an object identifier (we generally
> use the object class, primary key, and application identifier). This
> allows comments to be attached to anything, and not just specific
> table records. The comment schema and supporting java code is used
> by a number of applications without having any dependencies on these
> applications.
>
> Simplest use looks like..
> CommentListProvider.addComment(Object, text);
> List<Comment> list = CommentListProvider.getCommentList(Object);
>
>
> On Tue, Apr 23, 2013 at 9:21 AM, Markus Reich
> <ma...@markusreich.at> wrote:
> > ok, e.g. I have a table comments with following columns
> > ID => Unique key of table comments
> > OBJECT => Not null, identifies the entity the comment is for
> > OBJECTID => Not null, key of the entity
> > COMMENT => Comments as VARCHAR
> >
> > example data
> > 1 ORDER 12345 This is a comment for order 12345
> > 2 ORDER 56789 This is a comment for order 56789
> > 3 BILLING 11111 This is a comment for the billing 11111
> >
> > Now there's a table orders and a table billings (both have a unique ID
> > column), so I want relationships like
> > SELECT * FROM order JOIN comments ON comments.OBJECT = 'ORDER' AND
> > comments.ID = order.ID
> > and
> > SELECT * FROM billings JOIN comments ON comments.OBJECT = 'BILLING' AND
> > comments.ID = billing.ID
> >
> > Meex
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > 2013/4/23 Mike Kienenberger <mk...@gmail.com>
> >
> >> I don't quite understand the question you are asking.
> >>
> >> Can you give us an example of what you are trying to do with your
> >> comment table? Some code using your desired mapping would be best.
> >>
> >>
> >> On Tue, Apr 23, 2013 at 12:49 AM, Markus Reich
> >> <ma...@markusreich.at> wrote:
> >> > Hi,
> >> >
> >> > with the wonderful Cayenne Modeler it's possible to easily create
> >> relations
> >> > between DB entities.
> >> > In my case I have a central table for comments, with a key specifying
> the
> >> > content.
> >> > No I need to map this key field to a constant literal, as I know it is
> >> just
> >> > possible to join fields with fields of the target entity :-(
> >> > Is there a workaround (I don't like to make a lot of views an join
> them
> >> ;-))
> >> >
> >> > regards
> >> > Meex
> >>
> >
> >
> >
> > --
> > *Markus Reich*
> > Moosbach 28/2
> > 6392 St. Jakob i.H.
> > www.markusreich.at / www.meeximum.at
> > markus.reich@markusreich.at
>
--
*Markus Reich*
Moosbach 28/2
6392 St. Jakob i.H.
www.markusreich.at / www.meeximum.at
markus.reich@markusreich.at
Re: Relationship
Posted by Mike Kienenberger <mk...@gmail.com>.
Long ago, in Cayenne 1.1, I did something similar, although I don't
know if it's now the best way to do it, or if this will completely
solve your problem. I had defined a separate relationship using the
same db field for every object I wanted to relate to my entity.
I had a ChangeLog entity, similar to your Comments entity, and I
needed a relationship between it and every other existing entity.
Because I only needed to set the foreign key,
aChangeLogObject.setForeign<ENTITY>Object(new<ENTITY>);
I did not create the reverse changeLogList object relationships
because I did not need them.
In the model, it looks like this. Note that I had to create a new
relationship for each object:
==================
<obj-entity name="ChangeLog"
className="com.xyz.cayenne.ebpp.entity.ChangeLog"
lock-type="optimistic" dbEntityName="LOG">
<obj-attribute name="foreignRecordKey"
type="java.lang.Integer" lock="true"
db-attribute-path="FOREIGN_RECORD_KEY"/>
<obj-attribute name="tableName" type="java.lang.String"
lock="true" db-attribute-path="TABLE_NAME"/>
[...]
<obj-relationship name="foreignAccount" source="ChangeLog"
target="Account" db-relationship-path="foreignAccount"/>
<obj-relationship name="foreignAddress" source="ChangeLog"
target="Address" db-relationship-path="foreignAddress"/>
[...]
<db-relationship name="foreignAccount" source="LOG"
target="ACCOUNT" toMany="false">
<db-attribute-pair source="FOREIGN_RECORD_KEY" target="ACCOUNT_NUMBER"/>
</db-relationship>
<db-relationship name="foreignAddress" source="LOG"
target="ADDRESS" toMany="false">
<db-attribute-pair source="FOREIGN_RECORD_KEY" target="ID"/>
</db-relationship>
==================
In my latest work project, comments were set up differently. There
is no direct relationship between comments and other objects, either
in the database or in the code.
Instead, we have a CommentListProvider class which looks up all
comments attached to an object by an object identifier (we generally
use the object class, primary key, and application identifier). This
allows comments to be attached to anything, and not just specific
table records. The comment schema and supporting java code is used
by a number of applications without having any dependencies on these
applications.
Simplest use looks like..
CommentListProvider.addComment(Object, text);
List<Comment> list = CommentListProvider.getCommentList(Object);
On Tue, Apr 23, 2013 at 9:21 AM, Markus Reich
<ma...@markusreich.at> wrote:
> ok, e.g. I have a table comments with following columns
> ID => Unique key of table comments
> OBJECT => Not null, identifies the entity the comment is for
> OBJECTID => Not null, key of the entity
> COMMENT => Comments as VARCHAR
>
> example data
> 1 ORDER 12345 This is a comment for order 12345
> 2 ORDER 56789 This is a comment for order 56789
> 3 BILLING 11111 This is a comment for the billing 11111
>
> Now there's a table orders and a table billings (both have a unique ID
> column), so I want relationships like
> SELECT * FROM order JOIN comments ON comments.OBJECT = 'ORDER' AND
> comments.ID = order.ID
> and
> SELECT * FROM billings JOIN comments ON comments.OBJECT = 'BILLING' AND
> comments.ID = billing.ID
>
> Meex
>
>
>
>
>
>
>
>
>
> 2013/4/23 Mike Kienenberger <mk...@gmail.com>
>
>> I don't quite understand the question you are asking.
>>
>> Can you give us an example of what you are trying to do with your
>> comment table? Some code using your desired mapping would be best.
>>
>>
>> On Tue, Apr 23, 2013 at 12:49 AM, Markus Reich
>> <ma...@markusreich.at> wrote:
>> > Hi,
>> >
>> > with the wonderful Cayenne Modeler it's possible to easily create
>> relations
>> > between DB entities.
>> > In my case I have a central table for comments, with a key specifying the
>> > content.
>> > No I need to map this key field to a constant literal, as I know it is
>> just
>> > possible to join fields with fields of the target entity :-(
>> > Is there a workaround (I don't like to make a lot of views an join them
>> ;-))
>> >
>> > regards
>> > Meex
>>
>
>
>
> --
> *Markus Reich*
> Moosbach 28/2
> 6392 St. Jakob i.H.
> www.markusreich.at / www.meeximum.at
> markus.reich@markusreich.at
Re: Relationship
Posted by Aristedes Maniatis <ar...@maniatis.org>.
So what you are doing is creating a polymorphic join. One Cayenne way to model this is with inheritance and I've discussed this approach before:
http://markmail.org/message/6xdboo2ygrcmp6p3
Ari
On 23/04/13 11:21pm, Markus Reich wrote:
> ok, e.g. I have a table comments with following columns
> ID => Unique key of table comments
> OBJECT => Not null, identifies the entity the comment is for
> OBJECTID => Not null, key of the entity
> COMMENT => Comments as VARCHAR
>
> example data
> 1 ORDER 12345 This is a comment for order 12345
> 2 ORDER 56789 This is a comment for order 56789
> 3 BILLING 11111 This is a comment for the billing 11111
>
> Now there's a table orders and a table billings (both have a unique ID
> column), so I want relationships like
> SELECT * FROM order JOIN comments ON comments.OBJECT = 'ORDER' AND
> comments.ID = order.ID
> and
> SELECT * FROM billings JOIN comments ON comments.OBJECT = 'BILLING' AND
> comments.ID = billing.ID
>
> Meex
>
>
>
>
>
>
>
>
>
> 2013/4/23 Mike Kienenberger <mk...@gmail.com>
>
>> I don't quite understand the question you are asking.
>>
>> Can you give us an example of what you are trying to do with your
>> comment table? Some code using your desired mapping would be best.
>>
>>
>> On Tue, Apr 23, 2013 at 12:49 AM, Markus Reich
>> <ma...@markusreich.at> wrote:
>>> Hi,
>>>
>>> with the wonderful Cayenne Modeler it's possible to easily create
>> relations
>>> between DB entities.
>>> In my case I have a central table for comments, with a key specifying the
>>> content.
>>> No I need to map this key field to a constant literal, as I know it is
>> just
>>> possible to join fields with fields of the target entity :-(
>>> Is there a workaround (I don't like to make a lot of views an join them
>> ;-))
>>>
>>> regards
>>> Meex
>>
>
>
>
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: Relationship
Posted by Markus Reich <ma...@markusreich.at>.
ok, e.g. I have a table comments with following columns
ID => Unique key of table comments
OBJECT => Not null, identifies the entity the comment is for
OBJECTID => Not null, key of the entity
COMMENT => Comments as VARCHAR
example data
1 ORDER 12345 This is a comment for order 12345
2 ORDER 56789 This is a comment for order 56789
3 BILLING 11111 This is a comment for the billing 11111
Now there's a table orders and a table billings (both have a unique ID
column), so I want relationships like
SELECT * FROM order JOIN comments ON comments.OBJECT = 'ORDER' AND
comments.ID = order.ID
and
SELECT * FROM billings JOIN comments ON comments.OBJECT = 'BILLING' AND
comments.ID = billing.ID
Meex
2013/4/23 Mike Kienenberger <mk...@gmail.com>
> I don't quite understand the question you are asking.
>
> Can you give us an example of what you are trying to do with your
> comment table? Some code using your desired mapping would be best.
>
>
> On Tue, Apr 23, 2013 at 12:49 AM, Markus Reich
> <ma...@markusreich.at> wrote:
> > Hi,
> >
> > with the wonderful Cayenne Modeler it's possible to easily create
> relations
> > between DB entities.
> > In my case I have a central table for comments, with a key specifying the
> > content.
> > No I need to map this key field to a constant literal, as I know it is
> just
> > possible to join fields with fields of the target entity :-(
> > Is there a workaround (I don't like to make a lot of views an join them
> ;-))
> >
> > regards
> > Meex
>
--
*Markus Reich*
Moosbach 28/2
6392 St. Jakob i.H.
www.markusreich.at / www.meeximum.at
markus.reich@markusreich.at
Re: Relationship
Posted by Mike Kienenberger <mk...@gmail.com>.
I don't quite understand the question you are asking.
Can you give us an example of what you are trying to do with your
comment table? Some code using your desired mapping would be best.
On Tue, Apr 23, 2013 at 12:49 AM, Markus Reich
<ma...@markusreich.at> wrote:
> Hi,
>
> with the wonderful Cayenne Modeler it's possible to easily create relations
> between DB entities.
> In my case I have a central table for comments, with a key specifying the
> content.
> No I need to map this key field to a constant literal, as I know it is just
> possible to join fields with fields of the target entity :-(
> Is there a workaround (I don't like to make a lot of views an join them ;-))
>
> regards
> Meex