Home » RADICORE » How To » list2 not respecting list1 key
| list2 not respecting list1 key [message #727] |
Fri, 30 March 2007 09:56  |
rgervais
Messages: 7
Registered: March 2007
|
Junior Member |
|
|
First off, this is really a great product!
I have just a small problem with list2. I'm sure it's a simple setup issue. I am using postgres as the back end.
I have a restricted relationship that links jnr file log, to snr file period : period_id - log_backup_period.
I have a list2 that I call from a list1. Initally the list2, selects children from all the list1 records, even though I have only selected a specific list1 record, or period_id.
However....once in list2, if I click on any field heading to resort the list, only the correct records appear.
So it kinda works, but somehow I think that I really should not need to resort the list in order to get the correct records.
Thanks
|
|
|
|
| Re: list2 not respecting list1 key [message #728 is a reply to message #727] |
Fri, 30 March 2007 10:45  |
AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I cannot reproduce this problem at all. The framework has several examples of a List2 being called from a List1, and none of these exhibit the behaviour you describe. Take a look at the following which exist in the MENU subsystem:
- List Pattern, List Task by Pattern
- List Subsystem, List Task by Subsystem
- List Role, List User by Role
If these work OK then there is something wrong in your code. What string is being passed from the List1 to the List2 in $where, and is this string being modified before being used in the List2?
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: list2 not respecting list1 key [message #729 is a reply to message #728] |
Fri, 30 March 2007 13:12 |
rgervais
Messages: 7
Registered: March 2007
|
Junior Member |
|
|
Hmm those sample tables work right. I did not add any code to the function. It looks pretty much like the samples.
$outer_table = 'period'; // name of outer (parent) table
$inner_table = 'log'; // name of inner (child) table
$screen = 'log.list2.screen.inc'; // file identifying screen structure
require 'std.list2.inc'; // activate page controller
I decided I would try again in a new subsystem, all fresh and new. When I hit the popup on the jrn table in a create relationship, I get:
Fatal Error: pg_query() [function.pg-query]: Query failed: ERROR: 42703: column "column_count" does not exist LOCATION: transformColumnRef, parse_expr.c:396 (# 256).
SQL query: SELECT count(*) FROM dict_table LEFT JOIN dict_column ON (dict_table.database_id=dict_column.database_id AND dict_table.table_id=dict_column.table_id) WHERE dict_table.database_id='it' GROUP BY dict_table.database_id, dict_table.table_id, dict_table.table_desc HAVING column_count > 0
Error in line 295 of file '/srv/www/htdocs/radicore/includes/dml.pgsql.class.inc'.
Host Info: localhost, Server Version: 8.1.4
Client Encoding: UTF8, Server Encoding: LATIN1
Database: RADICORE, Schema: "DICT", PUBLIC
Script: /radicore/dict/table_popup.php
Request URI: /radicore/dict/table_popup.php?session_name=menu3
I'm not sure how I added them in the past, but clear other stuff is wrong.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: list2 not respecting list1 key [message #1852 is a reply to message #728] |
Sat, 15 November 2008 07:32 |
David Lee
Messages: 44
Registered: June 2006
|
Member |
|
|
I have this same problem appearing. Select a couple of items from a list1, then list2 display, and all records are displayed. Cause the query to be repeated, by changing sort order, or the next selected item from the list1, and the display is then correct.
The WHERE is completely dropped from the first screen, as seen in the mysql statements saved into physicalkey_b(list2).php I have included all that trace below, which also shows the other queries for look-up tables, and menu/user control, and added a few comments.
***** /njc/physicalkey_b(list2).php 2008-11-15 12:14:17
SELECT SQL_CALC_FOUND_ROWS * FROM mnu_control WHERE record_id='SYSTEM' AND field_id LIKE 'SHUTDOWN%' ORDER BY record_id, field_id =>Count=10
***** /njc/physicalkey_b(list2).php 2008-11-15 12:14:59
SELECT SQL_CALC_FOUND_ROWS * FROM mnu_control WHERE record_id='SYSTEM' AND field_id LIKE 'SHUTDOWN%' ORDER BY record_id, field_id =>Count=10
SELECT SQL_CALC_FOUND_ROWS field_id, initial_value, is_noedit FROM mnu_initial_value_user WHERE user_id='DNL' AND task_id='njcphysicalkey_b(list2)'
ORDER BY user_id, task_id, field_id =>Count=0
SELECT SQL_CALC_FOUND_ROWS field_id, initial_value, is_noedit FROM mnu_initial_value_role WHERE role_id='GLOBAL' AND task_id='njcphysicalkey_b(list2)'
ORDER BY role_id, task_id, field_id =>Count=0
SELECT SQL_CALC_FOUND_ROWS field_id, initial_value, is_noedit FROM mnu_initial_value_user WHERE user_id='DNL' AND task_id='njcphysicalkey_b(list2)'
ORDER BY user_id, task_id, field_id =>Count=0
SELECT SQL_CALC_FOUND_ROWS field_id, initial_value, is_noedit FROM mnu_initial_value_role WHERE role_id='GLOBAL' AND task_id='njcphysicalkey_b(list2)'
ORDER BY role_id, task_id, field_id =>Count=0
SELECT SQL_CALC_FOUND_ROWS location.* FROM location WHERE ( location.location_id='19' ) OR ( location.location_id='36' ) ORDER BY location.location_
name asc LIMIT 1 OFFSET 0 =>Count=2
The last line above shows the keys for the two selected items
SHOW COLUMNS FROM location LIKE 'organisation'=>Count=,JFC,The Deco,Administration
SELECT SQL_CALC_FOUND_ROWS physicalkey.*, keytype.user_name, location.location_name FROM physicalkey LEFT JOIN keytype ON (keytype.keytype_id=physicalk
ey.fkkeytype_id) LEFT JOIN location ON (location.location_id=physicalkey.fklocation_id) LIMIT 10 OFFSET 0 =>Count=595
No WHERE in the last line, so many results
SELECT SQL_CALC_FOUND_ROWS location_id, CONCAT(location_name,' ' , location_type) FROM location ORDER BY location.location_type =>Count=54
SELECT SQL_CALC_FOUND_ROWS keytype_id, CONCAT(marking, ' ', user_name ) FROM keytype ORDER BY keytype.sortorder =>Count=121
SELECT SQL_CALC_FOUND_ROWS role_id,global_access FROM mnu_role WHERE role_id='GLOBAL' ORDER BY role_id =>Count=1
SELECT SQL_CALC_FOUND_ROWS x.task_id, x.script_id, x.pattern_id, mnu_nav_button.button_text, mnu_nav_button.context_preselect FROM mnu_nav_button LEFT
JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task AS x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE mnu_task.t
ask_id='njcphysicalkey_b(list2)' AND (x.is_disabled='N') ORDER BY mnu_nav_button.sort_seq =>Count=5
Now select the next record.
***** /njc/physicalkey_b(list2).php 2008-11-15 12:15:15
SELECT SQL_CALC_FOUND_ROWS * FROM mnu_control WHERE record_id='SYSTEM' AND field_id LIKE 'SHUTDOWN%' ORDER BY record_id, field_id =>Count=10
SELECT SQL_CALC_FOUND_ROWS location.* FROM location WHERE ( location.location_id='19' ) OR ( location.location_id='36' ) ORDER BY location.location_
name asc LIMIT 1 OFFSET 1 =>Count=2
SHOW COLUMNS FROM location LIKE 'organisation'=>Count=,JFC,The Deco,Administration
SELECT SQL_CALC_FOUND_ROWS physicalkey.*, keytype.user_name, location.location_name FROM physicalkey LEFT JOIN keytype ON (keytype.keytype_id=physicalk
ey.fkkeytype_id) LEFT JOIN location ON (location.location_id=physicalkey.fklocation_id) WHERE location.location_id='36' LIMIT 10 OFFSET 0 =>Count=
0
WHERE in the last line limits it correctly, but, for this case, there are no valid records. That is not significant.
SELECT SQL_CALC_FOUND_ROWS location_id, CONCAT(location_name,' ' , location_type) FROM location ORDER BY location.location_type =>Count=54
SELECT SQL_CALC_FOUND_ROWS keytype_id, CONCAT(marking, ' ', user_name ) FROM keytype ORDER BY keytype.sortorder =>Count=121
SELECT SQL_CALC_FOUND_ROWS role_id,global_access FROM mnu_role WHERE role_id='GLOBAL' ORDER BY role_id =>Count=1
SELECT SQL_CALC_FOUND_ROWS x.task_id, x.script_id, x.pattern_id, mnu_nav_button.button_text, mnu_nav_button.context_preselect FROM mnu_nav_button LEFT
JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task AS x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE mnu_task.t
ask_id='njcphysicalkey_b(list2)' AND (x.is_disabled='N') ORDER BY mnu_nav_button.sort_seq =>Count=5
Now go back to the first key
***** /njc/physicalkey_b(list2).php 2008-11-15 12:15:34
SELECT SQL_CALC_FOUND_ROWS * FROM mnu_control WHERE record_id='SYSTEM' AND field_id LIKE 'SHUTDOWN%' ORDER BY record_id, field_id =>Count=10
SELECT SQL_CALC_FOUND_ROWS location.* FROM location WHERE ( location.location_id='19' ) OR ( location.location_id='36' ) ORDER BY location.location_
name asc LIMIT 1 OFFSET 0 =>Count=2
SHOW COLUMNS FROM location LIKE 'organisation'=>Count=,JFC,The Deco,Administration
SELECT SQL_CALC_FOUND_ROWS physicalkey.*, keytype.user_name, location.location_name FROM physicalkey LEFT JOIN keytype ON (keytype.keytype_id=physicalk
ey.fkkeytype_id) LEFT JOIN location ON (location.location_id=physicalkey.fklocation_id) WHERE location.location_id='19' LIMIT 10 OFFSET 0 =>Count=
4
WHERE in the last line so 4 records, not the 595 earlier ones.
SELECT SQL_CALC_FOUND_ROWS location_id, CONCAT(location_name,' ' , location_type) FROM location ORDER BY location.location_type =>Count=54
SELECT SQL_CALC_FOUND_ROWS keytype_id, CONCAT(marking, ' ', user_name ) FROM keytype ORDER BY keytype.sortorder =>Count=121
SELECT SQL_CALC_FOUND_ROWS role_id,global_access FROM mnu_role WHERE role_id='GLOBAL' ORDER BY role_id =>Count=1
SELECT SQL_CALC_FOUND_ROWS x.task_id, x.script_id, x.pattern_id, mnu_nav_button.button_text, mnu_nav_button.context_preselect FROM mnu_nav_button LEFT
JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task AS x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE mnu_task.t
ask_id='njcphysicalkey_b(list2)' AND (x.is_disabled='N') ORDER BY mnu_nav_button.sort_seq =>Count=5
|
|
|
|
| Re: list2 not respecting list1 key [message #1853 is a reply to message #1852] |
Sat, 15 November 2008 09:36 |
AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I cannot reproduce this problem. I have tested this using tasks in the XAMPLE subsystem, namely "List Person" (LIST1) and "List Person Address" (LIST2).
When I make a selection in the LIST1 screen it is passed to the LIST2 screen in the $selection variable, but is transferred to the $where variable. No matter what I do in the LIST2 screen - scrolling back and forth through the PERSON entries, changing the sort order of the ADDRESS entries, or navigating to a child screen such as "Enquire Address" - I cannot get the $where in the LIST2 screen to be modified in any way. It always remains as it was passed down from the parent LIST1 screen.
I suggest that you step through with your debugger to see where it is being changed, and what code is causing it.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: list2 not respecting list1 key [message #1855 is a reply to message #1854] |
Sun, 16 November 2008 12:22 |
AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
If the field name in the WHERE string which is extracted from the parent table is "location_id", but the corresponding field name in the child table is "FKlocation_id", then that is your problem. The framework assumes that fields which have the same meaning have the same name, and here they do not. You must insert custom code to manually change that entry in the WHERE string as the framework cannot do it for you.
You will find some examples of this in the MENU susbsystem - take a look inside the _cm_pre_getData() method of "mnu_nav_button_jnr.class.inc" and "mnu_nav_button_snr.class.inc" which show where the field name "task_id" is changed to either "task_id_jnr" to "task_id_snr".
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: list2 not respecting list1 key [message #2232 is a reply to message #1855] |
Sun, 19 July 2009 06:23 |
AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
The mechanics of passing context between objects - either in different tasks or the same task - is described in Passing context between objects.
If the same field has different names in different objects then pay attention to Different Names for the same field.
Note that although the $parent_relations array contains information regarding which fields are used in the relationship between two tables, or even where a table is related to itself, this is only used in the ON clause of a JOIN when that table object is asked to construct an sql SELECT statement prior to retrieving its own data. This information cannot be used to automatically translate field names in a WHERE string which is passed down from an external source as there is no information which identifies which of the many possible relationships is involved.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 07:04:40 EDT 2024
Total time taken to generate the page: 0.01424 seconds
|
Radicore Forum
Search
Help
Members
Register
Login
Home
