Inner join querys: two fields from one table

Hello,

Sorry for the strangely-worded question title. I've been searching
around for a few days here on the CakePHP doc book, CakePHP group, and
around the Internet but I haven't been able to find a tip on how to do
this.

I have a MySQL database with these tables (names are simplified):

+ school_classes
---- id
---- grade_from
---- grade_to

+ grade_levels
---- id
---- short_name

So basically I have a table that contains information for different
school-related classes. And I have another table that has a list of
grade levels that are available to choose when editing (or viewing in
this case,) school classes.

For example:

school_classes
id: 1
grade_from: 1
grade_to: 3

grade_levels
id: 1
short_name: K

id: 2
short_name: 1

id: 3
short_name: 2

would mean that I have a school class with a grade-level range of
Kindergarten (K) to Second Grade (2).

I know what type of MySQL query I am looking for... I am just unclear
about how to create the same behavior in CakePHP as far as my hasMany
or belongsTo behaviors might go.

Here is a query that shows what I'm trying to do here if that helps
(ClassSession = school_classes from example above. GradeLevel =
grade_levels from example above):

SELECT `ClassSession`.`id`, `ClassSession`.`title`,
`ClassSession`.`session_date`, `ClassSession`.`description`,
`ClassSession`.`speaker`, `ClassSession`.`grade_from`,
`ClassSession`.`grade_to`, `ClassSession`.`room`,
`ClassSession`.`time_block`, `ClassSession`.`owner`,
`ClassSession`.`created_at`, `ClassSession`.`modified_at`,
`Speaker`.`id`, `Speaker`.`first_name`, `Speaker`.`last_name`,
`Speaker`.`mailing_address`, `Speaker`.`city`, `Speaker`.`state`,
`Speaker`.`zip`, `Speaker`.`country`, `Speaker`.`email_address`,
`Speaker`.`phone_number`, `Speaker`.`affiliation`, `Speaker`.`owner`,
`Speaker`.`created_at`, `Speaker`.`modified_at`,
`GradeLevelFrom`.`id`, `GradeLevelFrom`.`short_name`,
`GradeLevelFrom`.`full_name`, `GradeLevelTo`.`id`,
`GradeLevelTo`.`short_name`, `GradeLevelTo`.`full_name` FROM
`class_sessions` AS `ClassSession` INNER JOIN `speakers` AS `Speaker`
ON (`Speaker`.`id` = `ClassSession`.`speaker`) INNER JOIN
`grade_levels` AS `GradeLevelFrom` ON (`GradeLevelFrom`.`id` =
`ClassSession`.`grade_from`) INNER JOIN `grade_levels` AS
`GradeLevelTo` ON (`GradeLevelTo`.`id` = `ClassSession`.`grade_to`)
WHERE 1 = 1;

The results look like this:

| id | title | session_date |
description | speaker | grade_from | grade_to |
room | time_block | owner | created_at |
modified_at | id | first_name | last_name | mailing_address |
city | state | zip | country |
email_address | phone_number | affiliation | owner |
created_at | modified_at | id | short_name | full_name | id |
short_name | full_name |
+----+----------------+---------------------
+-----------------------------+---------+------------+----------
+----------+------------+-------+---------------------
+---------------------+----+------------+-----------+-----------------
+------------------+----------+-------+--------------------------
+---------------------+--------------+-----------------+-------
+------------+-------------+----+------------+--------------+----
+------------+--------------+
| 1 | First Session | 2010-02-02 22:23:56 | This is the first
session. | 1 | 1 | 3 | Room 205 | NULL
| 1 | 2010-02-02 22:23:56 | 2010-02-02 22:23:56 | 1 | Speaker A
| Smith | 123 SW 1st | Colorado Springs | Colorado | 80995 |
United States of America | smitha@smitha.com | 111-222-3333 | A
Middle School | 1 | NULL | NULL | 1 | K |
Kindergarten | 3 | 2 | Second Grade |
| 2 | Second Session | 2010-02-02 22:23:56 | This is the second
session. | 2 | 1 | 6 | Room 207 | NULL
| 1 | 2010-02-02 22:23:56 | 2010-02-02 22:23:56 | 2 | Speaker B
| Tuttle | 245 SE 3st | Colorado Springs | Colorado | 80995 |
United States of America | tuttleb@tuttleb.com | 222-333-4444 | B
Middle School | 1 | NULL | NULL | 1 | K |
Kindergarten | 6 | 5 | Fifth Grade |

Is there any way I can do this natively in CakePHP?
Any tips would be appreciated.

Thanks,
andy

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en