Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » LIST3 linked from LIST1 (LIST3: As I scroll through the parents, or children, the grandchild does not change)
LIST3 linked from LIST1 [message #4813] Wed, 08 July 2015 19:08 Go to next message
rafs is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
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.


Re: LIST3 linked from LIST1 [message #4818 is a reply to message #4816] Thu, 09 July 2015 16:17 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
AJM wrote on Thu, 09 July 2015 05:44
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.


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 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
AJM wrote on Thu, 09 July 2015 05:44
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.


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 #4820 is a reply to message #4816] Thu, 09 July 2015 18:10 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
AJM wrote on Thu, 09 July 2015 05:44
... then you must convert the field name manually in your code ...


Where?
Re: LIST3 linked from LIST1 [message #4821 is a reply to message #4820] Thu, 09 July 2015 19:12 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Ok, I see your documentation, http://www.tonymarston.net/php-mysql/database-design.html#fi eld.names.and.content
I am renaming my db fields, and will try again.
Thanks for the documentation; I am learning a lot.

[Updated on: Thu, 09 July 2015 19:25]

Report message to a moderator

Re: LIST3 linked from LIST1 [message #4822 is a reply to message #4818] Fri, 10 July 2015 04:43 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
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.


Re: LIST3 linked from LIST1 [message #4823 is a reply to message #4819] Fri, 10 July 2015 04:52 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
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.


Re: LIST3 linked from LIST1 [message #4824 is a reply to message #4820] Fri, 10 July 2015 04:55 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
Registered: April 2006
Location: Surrey, UK
Senior Member
rafs wrote on Thu, 09 July 2015 23:10
AJM 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.


Re: LIST3 linked from LIST1 [message #4825 is a reply to message #4821] Fri, 10 July 2015 05:01 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
Registered: April 2006
Location: Surrey, UK
Senior Member
rafs wrote on Fri, 10 July 2015 00:12
Ok, I see your documentation, http://www.tonymarston.net/php-mysql/database-design.html#fi eld.names.and.content
I am renaming my db fields, and will try again.

It is not necessary to convert the column names in your database, you can convert the contents of the $where string instead. I have to do this in the framework in those places where I have two foreign keys on a table which link to the same primary key, such as in the MNU_MENU table where menu_id and task_id_jnr both link to MNU_TASK.task_id. In cases like this it is simply not possible to rename the two foreign keys to match the same primary key, so I must deal with this in the code.


Re: LIST3 linked from LIST1 [message #4835 is a reply to message #4813] Sat, 11 July 2015 18:19 Go to previous messageGo to next message
rafs is currently offline  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 Go to previous message
AJM is currently offline  AJM
Messages: 2275
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.


Previous Topic: How to bulk import my users into Radicore?
Next Topic: How to change the screen title of a list1 task
Goto Forum:
  


Current Time: Sun Nov 28 16:08:30 EST 2021

Total time taken to generate the page: 0.01067 seconds