You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Terry Jeske <te...@gmail.com> on 2006/12/21 01:34:17 UTC
Lookup tables
Hello,
I have been working some with Hibernate, and while I like a lot of what it
has to offer, but I am constantly frustrated with things that would be a no
brainier sql query, that takes hours if not days to try in shoehorn into
their paradigm. Here is a scenario that you cannot do in Hibernate and I am
wonder if it is possible in Cayenne:
Here is a table structure:
projects_table
--------------------------------
id int
projectname varchar
status int
status_table
------------------------------
projectId int (fkey back to projects_table id)
status_name varchar
I have a project object as with the following variables:
Project.java
---------------------------------
int id
String projectName
String statusName
Hibernate does not support this, and to get around it I have had to create a
view that hibernate pulls from. We like having the status_table look-up
because it makes it easy to return sorted lists of projects, while giving us
the freedom to change status text.
Re: Lookup tables
Posted by Terry Jeske <te...@gmail.com>.
Right you are! I was a bit sloppy with my description. Sorry about that.
On 12/20/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> Terry,
>
> Just noticed that according to your message, status_table has an FK
> to project... Wouldn't you want the opposite if a project has just
> one status? (other than that what others and myself suggested in this
> thread is still correct).
>
>
> projects_table
> --------------------------------
> id int (pk)
> projectname varchar
> status_id int (fkey to status_table id)
>
>
> status_table
> ------------------------------
> status_id int (pk)
> status_name varchar
>
>
> Andrus
>
>
> On Dec 21, 2006, at 2:34 AM, Terry Jeske wrote:
>
> > projects_table
> > --------------------------------
> > id int
> > projectname varchar
> > status int
> >
> >
> > status_table
> > ------------------------------
> > projectId int (fkey back to projects_table id)
> > status_name varchar
> >
>
>
Re: Lookup tables
Posted by Terry Jeske <te...@gmail.com>.
Great! I will have to look into this after the first of the year.
Thanks for the response.
On 12/22/06, Michael Gentry <bl...@gmail.com> wrote:
>
> This sounds like normal, standard, typical things one would want to
> do. Cayenne supports in-memory sorting, but it sounds like you want
> to do refreshes from the database, which is fine. Just add an
> ordering clause to your query each time. See if this page helps you:
>
> http://cwiki.apache.org/CAYDOC/using-orderings.html
>
> /dev/mrg
>
>
> On 12/21/06, Terry Jeske <te...@gmail.com> wrote:
> > Thank you all for your input. Michael asked me "What exactly do you
> > need?". Which is a good question:
> >
> > - We need a way to track projects.
> > - Projects will be displayed to the user in a grid, which should be
> > sortable by Name, and Status.
> > - Status values are: "Completed", "In Progress", "Not Started"
> > - The current status values may change over time. For example "Not
> > Started" may change to "TBD".
> > - The user can change the status of a project by selecting a value
> > from a HTML list box.
> > - An admin user can add (and edit) values to the status table through
> > a web form, and these changes are immediately available to the
> website -
> > i.e. list box values are populated from the database. Adding a new
> > Status requires no code changes, it is purely database driven.
> >
> > I know that this example is a bit contrived i.e. "status" would normally
> > stay a bit static. However, there are other cases where these values may
> > change, and having them in a Key table should not be a big deal (IMHO
> <g>).
> >
> > I should note that I am not a DBA, but I know enough to get by, usually
> by
> > running queries until I get the syntax right. My focus is on the
> integration
> > side of things, but like many of us, I have to wear many hats.
> >
> >
> > On 12/20/06, Juergen Saar <ju...@jsaar.org> wrote:
> > >
> > > I would it do in the Andrus-Style ...
> > >
> > > but it seems, as if the idea of terry is a freetext-status,
> > > that is continous changed during the lifecycle of a project.
> > > I wonder why this information is not placed directly in the
> project-table.
> > >
> > > 2006/12/21, Andrus Adamchik <an...@objectstyle.org>:
> > > >
> > > > Terry,
> > > >
> > > > Just noticed that according to your message, status_table has an FK
> > > > to project... Wouldn't you want the opposite if a project has just
> > > > one status? (other than that what others and myself suggested in
> this
> > > > thread is still correct).
> > > >
> > > >
> > > > projects_table
> > > > --------------------------------
> > > > id int (pk)
> > > > projectname varchar
> > > > status_id int (fkey to status_table id)
> > > >
> > > >
> > > > status_table
> > > > ------------------------------
> > > > status_id int (pk)
> > > > status_name varchar
> > > >
> > > >
> > > > Andrus
> > > >
> > > >
> > > > On Dec 21, 2006, at 2:34 AM, Terry Jeske wrote:
> > > >
> > > > > projects_table
> > > > > --------------------------------
> > > > > id int
> > > > > projectname varchar
> > > > > status int
> > > > >
> > > > >
> > > > > status_table
> > > > > ------------------------------
> > > > > projectId int (fkey back to projects_table id)
> > > > > status_name varchar
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Re: Lookup tables
Posted by Michael Gentry <bl...@gmail.com>.
This sounds like normal, standard, typical things one would want to
do. Cayenne supports in-memory sorting, but it sounds like you want
to do refreshes from the database, which is fine. Just add an
ordering clause to your query each time. See if this page helps you:
http://cwiki.apache.org/CAYDOC/using-orderings.html
/dev/mrg
On 12/21/06, Terry Jeske <te...@gmail.com> wrote:
> Thank you all for your input. Michael asked me "What exactly do you
> need?". Which is a good question:
>
> - We need a way to track projects.
> - Projects will be displayed to the user in a grid, which should be
> sortable by Name, and Status.
> - Status values are: "Completed", "In Progress", "Not Started"
> - The current status values may change over time. For example "Not
> Started" may change to "TBD".
> - The user can change the status of a project by selecting a value
> from a HTML list box.
> - An admin user can add (and edit) values to the status table through
> a web form, and these changes are immediately available to the website -
> i.e. list box values are populated from the database. Adding a new
> Status requires no code changes, it is purely database driven.
>
> I know that this example is a bit contrived i.e. "status" would normally
> stay a bit static. However, there are other cases where these values may
> change, and having them in a Key table should not be a big deal (IMHO <g>).
>
> I should note that I am not a DBA, but I know enough to get by, usually by
> running queries until I get the syntax right. My focus is on the integration
> side of things, but like many of us, I have to wear many hats.
>
>
> On 12/20/06, Juergen Saar <ju...@jsaar.org> wrote:
> >
> > I would it do in the Andrus-Style ...
> >
> > but it seems, as if the idea of terry is a freetext-status,
> > that is continous changed during the lifecycle of a project.
> > I wonder why this information is not placed directly in the project-table.
> >
> > 2006/12/21, Andrus Adamchik <an...@objectstyle.org>:
> > >
> > > Terry,
> > >
> > > Just noticed that according to your message, status_table has an FK
> > > to project... Wouldn't you want the opposite if a project has just
> > > one status? (other than that what others and myself suggested in this
> > > thread is still correct).
> > >
> > >
> > > projects_table
> > > --------------------------------
> > > id int (pk)
> > > projectname varchar
> > > status_id int (fkey to status_table id)
> > >
> > >
> > > status_table
> > > ------------------------------
> > > status_id int (pk)
> > > status_name varchar
> > >
> > >
> > > Andrus
> > >
> > >
> > > On Dec 21, 2006, at 2:34 AM, Terry Jeske wrote:
> > >
> > > > projects_table
> > > > --------------------------------
> > > > id int
> > > > projectname varchar
> > > > status int
> > > >
> > > >
> > > > status_table
> > > > ------------------------------
> > > > projectId int (fkey back to projects_table id)
> > > > status_name varchar
> > > >
> > >
> > >
> >
> >
>
>
Re: Lookup tables
Posted by Terry Jeske <te...@gmail.com>.
Thank you all for your input. Michael asked me "What exactly do you
need?". Which is a good question:
- We need a way to track projects.
- Projects will be displayed to the user in a grid, which should be
sortable by Name, and Status.
- Status values are: "Completed", "In Progress", "Not Started"
- The current status values may change over time. For example "Not
Started" may change to "TBD".
- The user can change the status of a project by selecting a value
from a HTML list box.
- An admin user can add (and edit) values to the status table through
a web form, and these changes are immediately available to the website -
i.e. list box values are populated from the database. Adding a new
Status requires no code changes, it is purely database driven.
I know that this example is a bit contrived i.e. "status" would normally
stay a bit static. However, there are other cases where these values may
change, and having them in a Key table should not be a big deal (IMHO <g>).
I should note that I am not a DBA, but I know enough to get by, usually by
running queries until I get the syntax right. My focus is on the integration
side of things, but like many of us, I have to wear many hats.
On 12/20/06, Juergen Saar <ju...@jsaar.org> wrote:
>
> I would it do in the Andrus-Style ...
>
> but it seems, as if the idea of terry is a freetext-status,
> that is continous changed during the lifecycle of a project.
> I wonder why this information is not placed directly in the project-table.
>
> 2006/12/21, Andrus Adamchik <an...@objectstyle.org>:
> >
> > Terry,
> >
> > Just noticed that according to your message, status_table has an FK
> > to project... Wouldn't you want the opposite if a project has just
> > one status? (other than that what others and myself suggested in this
> > thread is still correct).
> >
> >
> > projects_table
> > --------------------------------
> > id int (pk)
> > projectname varchar
> > status_id int (fkey to status_table id)
> >
> >
> > status_table
> > ------------------------------
> > status_id int (pk)
> > status_name varchar
> >
> >
> > Andrus
> >
> >
> > On Dec 21, 2006, at 2:34 AM, Terry Jeske wrote:
> >
> > > projects_table
> > > --------------------------------
> > > id int
> > > projectname varchar
> > > status int
> > >
> > >
> > > status_table
> > > ------------------------------
> > > projectId int (fkey back to projects_table id)
> > > status_name varchar
> > >
> >
> >
>
>
Re: Lookup tables
Posted by Juergen Saar <ju...@jsaar.org>.
I would it do in the Andrus-Style ...
but it seems, as if the idea of terry is a freetext-status,
that is continous changed during the lifecycle of a project.
I wonder why this information is not placed directly in the project-table.
2006/12/21, Andrus Adamchik <an...@objectstyle.org>:
>
> Terry,
>
> Just noticed that according to your message, status_table has an FK
> to project... Wouldn't you want the opposite if a project has just
> one status? (other than that what others and myself suggested in this
> thread is still correct).
>
>
> projects_table
> --------------------------------
> id int (pk)
> projectname varchar
> status_id int (fkey to status_table id)
>
>
> status_table
> ------------------------------
> status_id int (pk)
> status_name varchar
>
>
> Andrus
>
>
> On Dec 21, 2006, at 2:34 AM, Terry Jeske wrote:
>
> > projects_table
> > --------------------------------
> > id int
> > projectname varchar
> > status int
> >
> >
> > status_table
> > ------------------------------
> > projectId int (fkey back to projects_table id)
> > status_name varchar
> >
>
>
Re: Lookup tables
Posted by Andrus Adamchik <an...@objectstyle.org>.
Terry,
Just noticed that according to your message, status_table has an FK
to project... Wouldn't you want the opposite if a project has just
one status? (other than that what others and myself suggested in this
thread is still correct).
projects_table
--------------------------------
id int (pk)
projectname varchar
status_id int (fkey to status_table id)
status_table
------------------------------
status_id int (pk)
status_name varchar
Andrus
On Dec 21, 2006, at 2:34 AM, Terry Jeske wrote:
> projects_table
> --------------------------------
> id int
> projectname varchar
> status int
>
>
> status_table
> ------------------------------
> projectId int (fkey back to projects_table id)
> status_name varchar
>
Re: Lookup tables
Posted by Juergen Saar <ju...@jsaar.org>.
That much is true ... but ...
it would be nice if it was possible to describe
these things in the repository.
I've actualy created a database that holds this additional informations
and a little more like a meaningful names for attributes
and entities, and made a web-Interface using Googles
web-toolkit, so erveryone in our company, that can
login to this Webserver, is able to add such informations.
2006/12/21, Michael Gentry <bl...@gmail.com>:
>
> Making a getStatusName() and even a setStatusName() is fine for
> conveniently getting the status name (and not needing a local
> statusName instance variable), but if you need to use statusName in a
> query, that won't do what you want -- you still need to specify the
> relationships. Also, you had mentioned sorting. What exactly do you
> need? There are various ways of sorting things in Cayenne.
>
> /dev/mrg
>
>
> On 12/20/06, Aristedes Maniatis <ar...@ish.com.au> wrote:
> >
> > On 21/12/2006, at 11:34 AM, Terry Jeske wrote:
> >
> > > I have a project object as with the following variables:
> > >
> > > Project.java
> > > ---------------------------------
> > > int id
> > > String projectName
> > > String statusName
> > >
> > > Hibernate does not support this, and to get around it I have had to
> > > create a
> > > view that hibernate pulls from. We like having the status_table
> > > look-up
> > > because it makes it easy to return sorted lists of projects, while
> > > giving us
> > > the freedom to change status text.
> >
> >
> > Put this in Project.java if you want a convenience method for this
> > purpose.
> >
> > public String getStatusName() {
> > return getStatus().getName();
> > }
> >
> >
> > Ari Maniatis
> >
> > -------------------------->
> > ish
> > http://www.ish.com.au
> > Level 1, 30 Wilson Street Newtown 2042 Australia
> > phone +61 2 9550 5001 fax +61 2 9550 4001
> > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
> >
> >
> >
> >
> >
>
Re: Lookup tables
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah - creating a separate entity for Status and a simple one-to-many
relationship between Project and Status'es should solve the problem:
Project
String projectName
Status status
Status
String statusName
List projects
While you (Terry - the original poster) may want to choose Cayenne
over Hibernate for a number of other reasons, in this particular case
I think both Cayenne and Hibernate offer the same standard solution.
Cheers,
Andrus
On Dec 21, 2006, at 5:53 AM, Michael Gentry wrote:
> Making a getStatusName() and even a setStatusName() is fine for
> conveniently getting the status name (and not needing a local
> statusName instance variable), but if you need to use statusName in a
> query, that won't do what you want -- you still need to specify the
> relationships. Also, you had mentioned sorting. What exactly do you
> need? There are various ways of sorting things in Cayenne.
>
> /dev/mrg
>
>
> On 12/20/06, Aristedes Maniatis <ar...@ish.com.au> wrote:
>>
>> On 21/12/2006, at 11:34 AM, Terry Jeske wrote:
>>
>> > I have a project object as with the following variables:
>> >
>> > Project.java
>> > ---------------------------------
>> > int id
>> > String projectName
>> > String statusName
>> >
>> > Hibernate does not support this, and to get around it I have had to
>> > create a
>> > view that hibernate pulls from. We like having the status_table
>> > look-up
>> > because it makes it easy to return sorted lists of projects, while
>> > giving us
>> > the freedom to change status text.
>>
>>
>> Put this in Project.java if you want a convenience method for this
>> purpose.
>>
>> public String getStatusName() {
>> return getStatus().getName();
>> }
>>
>>
>> Ari Maniatis
>>
>> -------------------------->
>> ish
>> http://www.ish.com.au
>> Level 1, 30 Wilson Street Newtown 2042 Australia
>> phone +61 2 9550 5001 fax +61 2 9550 4001
>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>>
>>
>>
>>
>>
>
Re: Lookup tables
Posted by Michael Gentry <bl...@gmail.com>.
Making a getStatusName() and even a setStatusName() is fine for
conveniently getting the status name (and not needing a local
statusName instance variable), but if you need to use statusName in a
query, that won't do what you want -- you still need to specify the
relationships. Also, you had mentioned sorting. What exactly do you
need? There are various ways of sorting things in Cayenne.
/dev/mrg
On 12/20/06, Aristedes Maniatis <ar...@ish.com.au> wrote:
>
> On 21/12/2006, at 11:34 AM, Terry Jeske wrote:
>
> > I have a project object as with the following variables:
> >
> > Project.java
> > ---------------------------------
> > int id
> > String projectName
> > String statusName
> >
> > Hibernate does not support this, and to get around it I have had to
> > create a
> > view that hibernate pulls from. We like having the status_table
> > look-up
> > because it makes it easy to return sorted lists of projects, while
> > giving us
> > the freedom to change status text.
>
>
> Put this in Project.java if you want a convenience method for this
> purpose.
>
> public String getStatusName() {
> return getStatus().getName();
> }
>
>
> Ari Maniatis
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
>
>
>
>
Re: Lookup tables
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 21/12/2006, at 11:34 AM, Terry Jeske wrote:
> I have a project object as with the following variables:
>
> Project.java
> ---------------------------------
> int id
> String projectName
> String statusName
>
> Hibernate does not support this, and to get around it I have had to
> create a
> view that hibernate pulls from. We like having the status_table
> look-up
> because it makes it easy to return sorted lists of projects, while
> giving us
> the freedom to change status text.
Put this in Project.java if you want a convenience method for this
purpose.
public String getStatusName() {
return getStatus().getName();
}
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A