Losing $where when fixed selection is not present [message #2242] |
Mon, 10 August 2009 07:39 ![Go to next message Go to next message](/theme/default/images/down.png) |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
I have a bug that seems to have been introduced in a recent upgrade (it doesn't occur in my production environment running 1.48 but does occur in my dev environment running 1.50). I'm struggling to track where it is going wrong in debug - hoping you will be able to help me track it down...
I have a table of items and descriptions. I have a second table containing rdcaccount_id and the item_id from the first table, and amounts. So a user can specify their own amounts for the items in the master table.
Updating of the amounts works perfectly, and the framework handles the rdcaccount_id automatically.
I also have two output2 tasks to print reports of the charges
1) Current amounts only (by setting curr/hist = 'C' in fixed selection)
2) All amounts (leave fixed selection blank)
For some reason I have to pass the rdcaccount_id to these tasks manually using _cm_getWhere() to ensure I only get the relevant rows (is this a bug also?) - the framework is not providing it automatically. In 1.48 this approach works OK.
However, in 1.50 only the 'current amounts' report is working correctly. The 'all amounts' report is displaying amounts for all rdcaccount_id (seems to be being filtered out somewhere, but I can't find where). The only difference between the tasks is the presence of the fixed selection criteria.
Can you help?
Thanks,
Graham
|
|
|
|
|
|
|
Re: Losing $where when fixed selection is not present [message #2265 is a reply to message #2242] |
Mon, 17 August 2009 06:57 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
Tony, with further more detailed testing I've found that this isn't completely fixed under certain conditions.
If I run the OUTPUT2 task without making any specific selections (i.e. a complete report) the following SQL is generated:
SELECT cost_group_charge.*, cost_group.cost_group_desc, school.school_name
FROM cost_group_charge
LEFT JOIN cost_group ON (cost_group.cost_group_id=cost_group_charge.cost_group_id)
LEFT JOIN school ON (school.school_id=cost_group_charge.rdcaccount_id)
WHERE cost_group_charge.rdcaccount_id='7'
ORDER BY cost_group_charge.age_group_id, cost_group_seq_nbr, cost_group_charge.banner_req =>Count=86
However, if I make a couple of selections before running the report the SQL is:
SELECT cost_group_charge.*, cost_group.cost_group_desc, school.school_name
FROM cost_group_charge
LEFT JOIN cost_group ON (cost_group.cost_group_id=cost_group_charge.cost_group_id)
LEFT JOIN school ON (school.school_id=cost_group_charge.rdcaccount_id)
WHERE ( cost_group_charge.cost_group_id='SDA2' ) OR ( cost_group_charge.cost_group_id='SDA3' ) AND cost_group_charge.rdcaccount_id IN ('1', '7')
ORDER BY cost_group_charge.age_group_id, cost_group_seq_nbr, cost_group_charge.banner_req =>Count=6
This actually selects from other rdcaccount_id rows also. It needs the selections that are grouped with the OR statement to be bracketed together.
|
|
|
Re: Losing $where when fixed selection is not present [message #2266 is a reply to message #2265] |
Mon, 17 August 2009 08:49 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If the 'cost_group' table contains rdcaccount_id, and rdcaccount_id is part of the primary key, are you including rdcaccount_id in the select list of the inner entity of the LIST2 task?
When you mark rows as being selected in a LIST task it will extract the primary keys of the selected rows and place them in the $selection string, which will be used as the $where string in any child task. If any part of the primary key is not available in $this->fieldarray then it cannot be extracted, which means that the $selection string will not correctly identify the records which you selected.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Re: Losing $where when fixed selection is not present [message #2270 is a reply to message #2242] |
Mon, 17 August 2009 09:55 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
I actually run the output2 task from a list1 transaction based on the cost_group table.
The user can set their charges by selecting cost_groups and entering a list2 task:
The outer table is cost_group (key cost_group_id)
The inner table is cost_group_charge (key rdcaccount_id and cost_group_id + a couple of others)
The intention was for users to be able to print a full scale of charges for all cost groups by running the output2 task from the list1 without making any selections, and this works fine.
However, I can't prevent a user making a selection in the list1 screen before clicking the output2 button, and this is when it goes wrong.
Because of the unusual scenario linking tables, one without rdcaccount_id, maybe it isn't worth the effort of getting the framework to handle this automatically. However, I need to ba able to code to prevent the wrong data being presented to the users, hence the suggestion in my latest post.
Further to this, the output2 task is based on the inner table, but the button is placed on the list1 screen of the outer table.
[Updated on: Mon, 17 August 2009 09:58] Report message to a moderator
|
|
|
Re: Losing $where when fixed selection is not present [message #2271 is a reply to message #2270] |
Mon, 17 August 2009 11:09 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
This is where you are confusing me. In an earlier post you said that you were ativating the OUTPUT2 task from a LIST2 task (where the $inner entity contains rdcaccount_id), but now you are saying that this problem occurs when you are activating it from a LIST1 task (where the $main entity does NOT contain rdcaccount_id). This also means that you are using a navigation button in a parent task which works on a different table than the one in the child task, which in this case also means that the foreign key which is passed from the parent to the child is incomplete as it is missing rdcaccount_id.
I have played around with my code and have managed to get it to produce a WHERE string in the format:
(( cost_group_id='SDA2' ) OR (cost_group_id='SDA3' )) AND rdcaccount_id IN ('1', '7')
Try the attached file and see if it solves your problem.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Re: Losing $where when fixed selection is not present [message #2275 is a reply to message #2274] |
Mon, 17 August 2009 15:15 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
I am not seeing this at all in my testing. When the 2nd LIST2 is activated the $where string contains the primary key of the $outer entity from the 1st LIST2 and the $selection string contains whatever has been selected from the $inner entity (may be empty). These are the exact values which are made available to _cm_initialise() without being changed at all.
Are you sure you are using the new versions of both std.table.class.inc and std.list2.inc which I supplied you?
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
Re: Losing $where when fixed selection is not present [message #2288 is a reply to message #2287] |
Thu, 27 August 2009 11:55 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
It is the default behaviour to replace $where with $selection when $selection is not empty. In order to override this default you should place the following code in the outer/parent table of the MULTI2 task:
function _cm_initialise ($where, &$selection, $search)
// perform any initialisation for the current task.
// NOTE: $selection is passed by reference as it may be amended.
// NOTE: $search is only available for OUTPUT tasks.
{
$selection = null;
return $where;
} // _cm_initialise
This will then prevent $where from being replaced with the contents of $selection.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|