Re: [Fwd: SQL query question]
Re: [Fwd: SQL query question]
- Subject: Re: [Fwd: SQL query question]
- From: email@hidden
- Date: Mon, 10 Nov 2003 14:45:04 CDT
- Priority: 3 (Normal)
[demime could not interpret encoding binary - treating as plain text]
The typical way to use EOF (that's the framework used for database access
in WO) is NOT to write SQL yourself.
The typical way to do what you want would be to establish a relationship
from the Person entity to the Department entity. With a many-to-many
relationship such as you have here, you have two choices. If the
'linking' table only contains the pk's of the two linked rows, and
doesn't contain any other information you need to access, you can
'flatten' the many-to-many relationship, and then you can treat it in
code as if it were just a simple to-many relationship from Person to
Department, and another in the opposite direction. The existence of the
'linking' table will be basically hidden from you, but EOF will take care
of generating the proper SQL referencing the linking table, when you
reference the 'flattened' relationship.
Alternatley, especially if the 'linking' table does have information in
it you need (an 'order' number is typical, although probably doens't
exist in this case), you can avoid flattening the relationship, and leave
the linking table exposed.
Either way, once you have the relationships modeled properly, accessing a
Person's Department is trivial, you just follow the relationships and EOF
does the work of generating the proper SQL.
I reccomend the EOF book at:
http://developer.apple.com/documentation/Webobjects/Enterprise_Objects/index.html
Although it's unfortuantely more of a reference than a tutorial for the
beginner.
The EOF section of the "Web Applications" book at:
http://developer.apple.com/documentation/Webobjects/Web_Applications/index.html
provides a bit more accessible introduction, with some tutorial-esque
sections.
Here's the section of the book specifically on relationships, although
I'm not sure if it covers flattened relationships:
http://developer.apple.com/documentation/Webobjects/Web_Applications/Relationships/index.html
There are some third-party books on WO out these days that are pretty
good too.
--Jonathan
On Mon, 10 Nov 2003 13:54:51 -0500 "Janice M. Cheung" wrote:
> Greetings!
>
> I have a question about sql queries.
>
> Upon login, I would like my application to return all the affiliated
> departments of a user.
> This list of affiliated departments will be matched against the
> user's Person Primary Key,
> personPk, in the Person Table. The linking table between the
> affiliated departments,
> Affiliation, has the personFk and the departmentFk, and the
> Department table has the
> departmentPk.
>
> I want to do something like this:
>
> sql = new String ("select distinct Department_Name from
> people_db.Department");
> sql = sql + "where person.personPk = affiliation.personFk and
> affiliation.departmentFk = department.departmentPk";
>
> - if department Administrator:
>
> - sql = sql + "where person.personPk = deptAdmin.personFk and
> deptAdmin.deptFk = department.departmentPk";
>
> but I'm having problems with the implementation... I've flattened
> the attribute relationships already, but even though
> I've set the person Primary Keys, the department lists are not
> appearing.
>
> If anyone can help me so that I can properly code this, I'd be
> eternally grateful!!
>
> Thanks very much!!
>
> Janice
> _______________________________________________
> webobjects-dev mailing list | email@hidden
> Help/Unsubscribe/Archives:
> http://www.lists.apple.com/mailman/listinfo/webobjects-dev
> Do not post admin requests to the list. They will be ignored.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.