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

Home » RADICORE development » Transaction Patterns » List2 with multiple parent relations to the same table
List2 with multiple parent relations to the same table [message #1770] Wed, 29 October 2008 14:18 Go to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
I have a table for organizations, and a table for vehicles. There are 3 links from the vehicle table to the organization table:

vehicle.manuf_organization_id ==> organization.organization_id
vehicle.owner_organization_id ==> organization.organization_id
vehicle.operator_organization_id ==> organization.organization_id

I am trying to set up a vehicle(list2) navigation button from my organization(list1) screen, so that the owner_organization_id is taken from the organization_id of the selected organization record. The controller for the vehicle(list2) is using the aliased organization table:
$outer_table = 'owner_organization';
The organization details are correctly displayed at the top of the list2 page.

However, executing an add2 from this list2 transaction produces a "No parent has been selected yet."

I assume I need to do something to the controller file for either the vehicle(list2) or organization(list1) to make the translation between organization_id and owner_organization_id work. But what is it?
Re: List2 with multiple parent relations to the same table [message #1773 is a reply to message #1770] Wed, 29 October 2008 15:04 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2244
Registered: April 2006
Location: Surrey, UK
Senior Member
I suggest that you step through with your debugger to see what is causing the "No parent has been selected yet" message.

It sounds like the $where string which is being passed from the LIST2 to the ADD2 does not contain any data which can be used by the ADD2 task.


Re: List2 with multiple parent relations to the same table [message #1774 is a reply to message #1773] Wed, 29 October 2008 15:35 Go to previous messageGo to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
It looks like the LIST2 is not getting the owner_organization_id either; the screen shows all the records from the vehicle table, not simply those associated with the selected organization.

Outer SQL:
SELECT SQL_CALC_FOUND_ROWS 
    organization.*, 
    parent_organization.name as parent_organization_name 
FROM organization  
    LEFT JOIN organization AS parent_organization 
        ON (parent_organization.organization_id=organization.parent_organization_id)
WHERE ( organization.organization_id='2' )    
ORDER BY organization.name asc
LIMIT 1 
OFFSET 0

Inner SQL:
SELECT SQL_CALC_FOUND_ROWS 
    vehicle.*, 
    manuf_organization.name AS organization_name, 
    owner_organization.name AS owner_organization_name, 
    operator_organization.name AS operator_organization_name 
FROM vehicle 
    LEFT JOIN organization AS manuf_organization 
        ON (manuf_organization.organization_id=vehicle.manuf_organization_id)  
    LEFT JOIN organization AS owner_organization 
        ON (owner_organization.organization_id=vehicle.owner_organization_id)  
    LEFT JOIN organization AS operator_organization 
        ON (operator_organization.organization_id=vehicle.operator_organization_id)      
LIMIT 25 
OFFSET 0


Where does the inner sql statement of vehicle(list2) get the owner_organization_id field from -- the outer sql statement of vehicle(list2), or the data posted from organization(list1)?
Re: List2 with multiple parent relations to the same table [message #1775 is a reply to message #1774] Wed, 29 October 2008 16:00 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2244
Registered: April 2006
Location: Surrey, UK
Senior Member
The $where clause of the outer entity in the LIST2 is supplied by the task from which the LIST2 was activated. This is used to retrieve a row from the outer entity (there may be multiple rows, but only one can be current at any one time). The primary key of the current row in the OUTER entity is then extracted into another $where string and passed to the inner entity.

When you press the ADD2 button in a LIST2 task the $where string which was input into the outer entity is passed into the AD2 task.


Re: List2 with multiple parent relations to the same table [message #1777 is a reply to message #1775] Wed, 29 October 2008 18:42 Go to previous messageGo to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
OK. In this case, the important field in $where that is passed to my LIST2 is called "organization_id", which is different from the name of the field it relates to ("owner_organization_id").

Does Radicore convert the field name in $where automatically -- based on the relationships defined in the .dict file -- or do I need to do it myself somewhere?
Re: List2 with multiple parent relations to the same table [message #1778 is a reply to message #1777] Wed, 29 October 2008 19:07 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2244
Registered: April 2006
Location: Surrey, UK
Senior Member
Radicore cannot translate any field names passed in the WHERE string between one object and another as there is no way of knowing which relationship is involved. Relationship details are only used when building an SQL statement. See FAQ129 for details.

You will need to translate the field name yourself. There is an example in the MENU system in the _cm_pre_getData() method of class mnu_menu.class.inc.


[Updated on: Sat, 07 November 2009 03:39]

Report message to a moderator

icon7.gif  Re: List2 with multiple parent relations to the same table [message #1783 is a reply to message #1778] Thu, 30 October 2008 11:51 Go to previous message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
PERFECT!

I added a _cm_pre_getData function to my vehicle class and everything works as expected:
    function _cm_pre_getData ($where, $where_array, $fieldarray=null)
    // perform custom processing before database record(s) are retrieved.
    // (WHERE is supplied in two formats - string and array)
    // $fieldarray may contain full details of the current record in the parent
    // class, not just its primary key.
    {
        $pattern_id = getPatternId();
        switch (strtolower($pattern_id)) {
            // may need to change the contents of $where
            case 'list2':
                // alter key
                $where = str_replace('organization_id=', 'owner_organization_id=', $where);
                break;

            default:
        } // switch

        return $where;

    } // _cm_pre_getData
Previous Topic: hyperlink in list or detail or tree form
Next Topic: PDF Report
Goto Forum:
  


Current Time: Wed May 12 17:46:57 EDT 2021

Total time taken to generate the page: 0.01652 seconds