| LIST3 linked from LIST1 [message #4813] |
Wed, 08 July 2015 19:08  |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
LIST3: As I scroll through the parents, or children, the grandchild does not change
parent --< children --< grandchildren
The parent and child data changes when I scroll at those levels, but grandchild remains unchanged.
I have linked this LIST3 from the parent's LIST1.
|
|
|
|
| Re: LIST3 linked from LIST1 [message #4816 is a reply to message #4813] |
Thu, 09 July 2015 05:44  |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
As you scroll through the parent entries the primary key of the current parent is passed down to child entity where it will enable you to scroll through all available entries one at a time. The primary key of the current child entity is then passed down to the grandchild entity where it will enable you to scroll through all available entries one page at a time.
You need to ensure that the SELECT list in each SQL query includes the primary key for that table. If you miss this out there is no value which can be passed down to the chiold entity.
If the are any differences between the name of the primary key in the parent table and the name of the corresponding foreign key inb the child table, then you must convert the field name manually in your code as the framework cannot do this automatically.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: LIST3 linked from LIST1 [message #4818 is a reply to message #4816] |
Thu, 09 July 2015 16:17 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
AJM wrote on Thu, 09 July 2015 05:44If the are any differences between the name of the primary key in the parent table and the name of the corresponding foreign key inb the child table, then you must convert the field name manually in your code as the framework cannot do this automatically.
I am following the convention that every table's primary key is called 'id', and every table's foreign key is called <referenced-table>_id. This is the convention that I picked up from CakePHP, and probably RoR. This convention was a requirement for the project. So, for example, packages has many files (WHERE packages.id = files.package_id), and files have many lines (WHERE files.id = lines.file_id).
The parent to child part works when using this convention, but that the child to grandchild linking does not.
[Updated on: Thu, 09 July 2015 16:50] Report message to a moderator |
|
|
|
| Re: LIST3 linked from LIST1 [message #4819 is a reply to message #4816] |
Thu, 09 July 2015 16:55 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
AJM wrote on Thu, 09 July 2015 05:44If the are any differences between the name of the primary key in the parent table and the name of the corresponding foreign key inb the child table, then you must convert the field name manually in your code as the framework cannot do this automatically.
I thought that the point of defining the relationships in the data dictionary was to handle these linking fields for me? When I defined the child relationships for the parent tables, it explicitly asks me to specify which field in the junior table is the foreign key to the primary key of the senior table, so this mapping should be handled automatically by the framework, no?
For example, Figure 13 in the first tutorial. Your xample db x_option table is linked tho x_pers_opt_xref WHERE "x_option.option_id = x_pers_opt_xref.option_id". I assumed that the fields need not have the same name.
[Updated on: Thu, 09 July 2015 17:00] Report message to a moderator |
|
|
|
|
|
|
|
| Re: LIST3 linked from LIST1 [message #4822 is a reply to message #4818] |
Fri, 10 July 2015 04:43 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
rafs wrote on Thu, 09 July 2015 21:17
I am following the convention that every table's primary key is called 'id', and every table's foreign key is called <referenced-table>_id. This is the convention that I picked up from CakePHP, and probably RoR. This convention was a requirement for the project. So, for example, packages has many files (WHERE packages.id = files.package_id), and files have many lines (WHERE files.id = lines.file_id).
That may be a convention that some newbies with lesser experience follow, but the convention that I and my fellow developers have been following since the 1970s is that wherever possible the same column should have the same name regardless of which table it's on. All the SQL samples you see on any vendor's website still follow this convention, which is why you see "customer.customer_id" and not "customer.id". Note also the use of snake_case and not CamelCaps.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: LIST3 linked from LIST1 [message #4823 is a reply to message #4819] |
Fri, 10 July 2015 04:52 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
rafs wrote on Thu, 09 July 2015 21:55
I thought that the point of defining the relationships in the data dictionary was to handle these linking fields for me? When I defined the child relationships for the parent tables, it explicitly asks me to specify which field in the junior table is the foreign key to the primary key of the senior table, so this mapping should be handled automatically by the framework, no?
Not quite correct. This dictionary information can be used when including JOINs into an SQL query as the code iterates through ALL the parent_relation entries. This is documented in Using Parent Relations to construct sql JOINs. When receiving a $where string from another object I do not know which table this came from or which of the possible relationships to use.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: LIST3 linked from LIST1 [message #4824 is a reply to message #4820] |
Fri, 10 July 2015 04:55 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
rafs wrote on Thu, 09 July 2015 23:10AJM wrote on Thu, 09 July 2015 05:44... then you must convert the field name manually in your code ...
Where?
If you look in /menu/classes/mnu_nav_button_jnr.class.inc and /menu/classes/mnu_nav_button_snr.class.inc you will see code in the _cm_pre_getData() method which does this. You could also use the _cm_initialise() method if you wished.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: LIST3 linked from LIST1 [message #4835 is a reply to message #4813] |
Sat, 11 July 2015 18:19 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
I originally skipped over your articles on database stuff. I have read them now. I guess I am rated as a novice with borderline nincompoop tendencies.
Anyways, I didn't see anything explicitly about the following (hypothetical) situation. A parent has many children, but always has a favorite (favourite) child. Are there any special considerations to be aware of? I would assume that I can just define each of these FK relationship in the data dictionaries, but I wonder if I will have to add some code since parent.child_id_favorite and child.child_id have different names.
parent
------
parent_id int primary key,
parent_name char(10),
child_id_favorite int,
FOREIGN KEY (child_id_favorite) REFERENCES child(child_id)
child
-----
child_id int primary key,
parent_id int,
child_name char(10),
FOREIGN KEY (parent_id) REFERENCES parent(parent_id)
Thanks.
[Updated on: Sat, 11 July 2015 18:22] Report message to a moderator |
|
|
|
| Re: LIST3 linked from LIST1 [message #4836 is a reply to message #4835] |
Sun, 12 July 2015 05:28 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
What you have there is a circular relationship, but having a foreign key linking to a primary key with a different name is something that occurs more than once in the MENU database. For example:
parent (MNU_TASK)
-----------------
task_id VARCHAR(80)
child (MNU_MENU)
----------------
menu_id VARCHAR(80) REFERENCES parent(task_id)
task_id_jnr VARCHAR(80) REFERENCES parent(task_id)
A relationship is a relationship, even if it is circular, so you handle it as any other relationship.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
