Radicore Forum: How To » list2 not respecting list1 key

Home » RADICORE » How To » list2 not respecting list1 key

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: list2 not respecting list1 key [message #728 is a reply to message #727]

Fri, 30 March 2007 10:45

AJM
Messages: 2382
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

Report message to a moderator

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.

Report message to a moderator

Re: list2 not respecting list1 key [message #730 is a reply to message #729]

Fri, 30 March 2007 13:45

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

This sounds like the bug reported in http://www.radicore.org/forum/index.php?t=msg&th=201 which has been fixed in the latest release (1.23.0) Can you install that and try again?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: list2 not respecting list1 key [message #731 is a reply to message #730]

Fri, 30 March 2007 16:33

rgervais
Messages: 7
Registered: March 2007

Junior Member

EXCELLENT!

I overlaid the new version on top of the old. I no longer get the error when defining a relationship. I still have the problem with the children in my first attempt, however I created a new function, and it works as it should. So I won't worry too much about the old one.

Thank you!

Report message to a moderator

Re: list2 not respecting list1 key [message #732 is a reply to message #731]

Fri, 30 March 2007 16:59

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

I'm glad it's fixed. I hope you can continue using Radicore without any more problems.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: list2 not respecting list1 key [message #733 is a reply to message #732]

Sat, 31 March 2007 07:54

rgervais
Messages: 7
Registered: March 2007

Junior Member

Just as a side note, this new version does not seem to allow me to import columns of type boolean.

Seq: 7, user_size: user_size cannot be blank.
Seq: 7, col_maxsize: col_maxsize cannot be blank.
No entries found on DICT_COLUMN

I'm using phppgadmin to create the tables, which does not allow me to enter a size for a column of type boolean.

I'll switch it to another type, but I thought I would let you know.

Regards
Bob

Report message to a moderator

Re: list2 not respecting list1 key [message #734 is a reply to message #733]

Sat, 31 March 2007 08:22

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

This should be a new topic, not a continuation of a previous topic.

For supported datatypes across the various database engines please refer to http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq76

I usually create my databases in MySQL before porting them to the others, so I have never had this problem. I will look into it though, to see if I can get the postgresql driver to allow the boolean datatype.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: list2 not respecting list1 key [message #735 is a reply to message #734]

Sat, 31 March 2007 10:46

AJM
Messages: 2382
Registered: April 2006
Location: Surrey, UK

Senior Member

Try the attached file to see if it will deal correctly with boolean fields.

Attachment: dml.pgsql.class.inc
(Size: 63.88KB, Downloaded 1396 times)

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: list2 not respecting list1 key [message #738 is a reply to message #735]

Sun, 01 April 2007 20:44

rgervais
Messages: 7
Registered: March 2007

Junior Member

Thanks, that update restored the importing of booleans.

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

***** /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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

 ***** /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.

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

***** /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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: list2 not respecting list1 key [message #1853 is a reply to message #1852]

Sat, 15 November 2008 09:36

AJM
Messages: 2382
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

Report message to a moderator

Re: list2 not respecting list1 key [message #1854 is a reply to message #1853]

Sun, 16 November 2008 12:02

David Lee
Messages: 44
Registered: June 2006

Member

I believe the problem is cause by different field names in the parent and child tables. The where string passed down is "location_id = '55'", into _sqlAssembleWhere and filterWhere. However, the correct column in the inner table is FKlocation_id, and the field name location_id is valid only for the outer table. The function fails to qualify location_id with the table name, and then deletes the location_id as that field is not in the table.

This difficulty is not found if the same field name is used in both tables.

I have not stepped through to understand why it works correctly on the second record of the outer table, but trust these details are enough to allow you to reproduce and solve this problem.

Report message to a moderator

Re: list2 not respecting list1 key [message #1855 is a reply to message #1854]

Sun, 16 November 2008 12:22

AJM
Messages: 2382
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

Report message to a moderator

Re: list2 not respecting list1 key [message #2232 is a reply to message #1855]

Sun, 19 July 2009 06:23

AJM
Messages: 2382
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

Report message to a moderator

Previous Topic:	Update1 problem
Next Topic:	Intranet Application

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

PDF

]

Current Time: Fri Jun 20 16:01:02 EDT 2025

Total time taken to generate the page: 0.09623 seconds