| Display field from related table [message #298] |
Tue, 03 October 2006 21:35  |
Acharn
Messages: 6
Registered: August 2006
Location: Thailand
|
Junior Member |
|
|
I've been using the example of the Ruby on Rails Cookbook to try to get a handle on using your framework, and I've hit on a snag. The database contains two tables: categories and recipes. Categories has two fields: id and name. Recipes has 6 fields: id, title, description, date, instructions, and category_id. Category_id is a foreign key to the categories table.
I've managed to get the basic menu and process scripts working, but in the screens which display data from the recipes table I want to show the name of the category, not its index number. I figured I could examine your solutions and chose the Classrooms prototype. The screen listing Lessons looked good, because you display the name of the teacher, rather than the teacher_id. But when I examine lesson_list.php and the associated crs_lesson.list.screen.inc I don't find any clue. The way the SQL is constructed is helpful on its own, but I expected some reference to the crs_teacher table to retrieve the name field.
The teacher's name displayed in the lesson_list screen is a nicely formatted string made from the three fields crs_teacher.title, crs_teacher.first_name, and crs_teacher.last_name. How did you do it?
Roger
|
|
|
|
| Re: Display field from related table [message #299 is a reply to message #298] |
Wed, 04 October 2006 04:44  |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
To answer your question about the formatted teacher name which appears in the lesson_list screen, first go to the Data Dictionary and take a look at the definition of the 'teacher_id' column in the 'crs_lesson' table. You will see that the control type is 'dropdown list' and the option list is 'teacher_id'. In the '_cm_getExtaData()' method in the 'crs_lesson' class you will see where it calls the 'getValRep()' method on an instance of the 'crs_teacher' class to obtain the contents of an array of data which it loads into $this->lookup_data['teacher_id']. If you examine the '_cm_getValRep()' method in the 'crs_teacher' class you will see how this array is constructed. When a dropdown list control is read-only, such as in a LIST screen, the entire dropdown list is not displayed, but the selected entry from the list is. That is how 'teacher_id' is automatically shown as the teacher's name.
To achieve the same sort of effect without using a dropdown list control is easy. Basically it requires a JOIN, which can be performed in one of two ways as outlined in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq18a and http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq18b.
To get the framework to perform the JOIN automatically you must go to the Data Dictionary and define the relationship between the two tables, as documented in http://www.tonymarston.net/php-mysql/menuguide/dict_relation ship(upd).html. This is where you say 'tableA is related to tableB, and the primary key field(s) of tableA are linked to so-and-so foreign key field(s) of tableB'. Notice also on this screen there is a column called 'parent field'. This is where you tell the framework to include that field from the parent table when you retrieve occurrences from the child table. You can either pick a single field from the dropdown list, or you can select the keyword 'CALCULATED' and fill in the details for 'calculated field'. Don't forget to export any changes you make using http://www.tonymarston.net/php-mysql/menuguide/dict_table(ex port).html so that the table structure file is updated.
If you do not set up the relationship details in the Data Dictionary so that the framework can perform the JOIN automatically, then you will have to do it manually, as outlined in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq08.
I hope this answers your question.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Display field from related table [message #300 is a reply to message #299] |
Wed, 04 October 2006 22:11 |
Acharn
Messages: 6
Registered: August 2006
Location: Thailand
|
Junior Member |
|
|
Thanks for the quick response. Lots of meat for me to chew on, here. My first impression is that writing the JOIN would be the more convenient approach, but my goal is an application similar to but even more complex than your Classroom prototype so having the alternative approaches is good. Thanks again.
Roger
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
