Re: Lookup tables
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]: 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
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]: 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
Right you are! I was a bit sloppy with my description. Sorry about that. On 12/20/06, Andrus Adamchik [EMAIL PROTECTED] 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
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
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 [EMAIL PROTECTED] 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
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 [EMAIL PROTECTED] 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
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
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 [EMAIL PROTECTED]: 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 [EMAIL PROTECTED] 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
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 [EMAIL PROTECTED]: 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