JOIN Operations

This section deals with LEFT joins.

selectExpandedObject

selectExpandedObjectArray

selectExpandedObjectByID

selectExpandedObjectCount

From DBUtil.class.php

Creating _objJoin arrays

The references in 'The structure of the parameters joinInfo?..' did not immediatly make sense to me as written in the above 'From DBUtil.class.php' comments, yet I now have a fully functional 3 array/table '_joinInfo?'. I'm going to try to describe in my own words how I now understand _joinInfo?.

First of all each module class has it's own _objType? which is just an alias for the primary tables pntables array index name (as set in the modules pntables.php).

See this example: http://community.postnuke.com/module-Forum-viewtopic-topic-53347-start-0.htm∞

My translation of the documentation

The documentation says this:
'join_table' => 'The tablekey to join to'
I say:
'join_table' => 'The table name as set in the modules pntables array you want to join on to the original '_objType?' table'

The documentation says this:
'join_field' => 'The field key of the field to join, can also be an array of fields',
I say:
The field, or an array of fields as set in the modules pntables array you want to extract from the join table and join to the original '_objType?' table'

The documentation says this:
'object_field_name' => 'The resulting field name, can also be an array if join_field is an array',
I say:
'object_field_name ' => 'The aliases that 'object_field_name' will appear as in the final object. This/ese will be the key references of the object array being returned that represent the actual pntable keys, or 'object_field_name', of the table being joined.

For example; the secondary join table has pntable keys set to 'tblID?' and 'field1?', but you want the resulting object to index these as 'ID' and 'SomethingElse?'. you would set 'join_field' to 'array(' tblID?', field1?)', and 'object_field_name' to array('ID', ' SomethingElse?'). If your only grabbing a single field from the secondary table then you would use single strings in this example as opposed to arrays,

The documentation says this:
'compare_field_table' => 'The compare field key (select table)',
I say:
'compare_field_table' => 'The field as set in the modules pntables array you want to join to in the original '_objType?' table',

The documentation says this:
'compare_field_join' => 'The compare field key (join table)');
I say:
'compare_field_join' => 'The field as set in the modules pntables array you want to join to in the secondary table (as set in 'join_table' =>).

Example of usage

First of all each module class has it's own _objType? which is just an alias for the primary tables pntables array index name (as set in the modules pntables.php file).

Using RosterMaster? as an example I have done this:

My tables are set up as such (shortened for brevity)...

The _objType? is set to 'rostermaster_members' so it is being used as the primary table and all fields are called in this case. Now I want to add in some info to each member array from the 'rostermaster_object' table. My first '_objJoin?' array looks like this...

'join_table' is obviously the table name of the secondary table.

'join_field' is the table fields from the 'rostermaster_object' table that are required.

'object_field_name' represents what ' join_field' will be called in the resulting array. Which takes care of a problem I would have otherwise had since there are duplicate field names between the 2 tables. And since this is a 'game specific' object I take advantage of the opportunity to appropriately name them... nifty eh?

'compare_field_table' is the id field name you want joined/referenced from the primary table '_objType?', in this case I used identical field names for clarities sake.

'compare_field_join' is the name of the id field in the secondary table that needs to match the value of ' compare_field_table'

Ok... so now I want to add items from the 'rostermaster_users' table. So my second '_objJoin?' array looks like this...

'join_table' is obviously the table name of the tertiary (third) table.

'join_field' is the table fields from the 'rostermaster_users' (tertiary (third)) table that are required.

'object_field_name' represents what ' join_field' will be called in the resulting array.

'compare_field_table' is the id field name you want joined/referenced from the primary table '_objType?', in this case I used identical field names for clarities sake.

'compare_field_join' is the name of the id field in the tertiary (third) table that needs to match the value of ' compare_field_table'... nice eh?

Ok... so now I got greedy. I wanted the PostNuke username, as associated to via 'rostermaster_users' table ' UserID?' added to this object without having to store it myself... I hate redundancy. Using pntables.php from the user module for reference, in my third '_objJoin?' array looks like this...

'join_table' is obviously the table name of the quaternary(fourth(pn_users)) table.

'join_field' is the table fields from the 'users' (quaternary (fourth (pn_users))) table that are required. In this case I only require one field so it is a string rather than an array.

'object_field_name' represents what ' join_field' will be called in the resulting array. In this case I only require one field so it is a string rather than an array.

'compare_field_table' is the id field name you want joined/referenced from, in this case, the 'rostermaster_users' (tertiary (third))table.

Oddly enough I could not use the $pntables field reference for the field name even though other references work, I had to usse the actual table name. I will likely file a bug report on this.

'compare_field_join' is the name of the id field in the tertiary (third) table that needs to match the value of 'users' (quaternary (fourth (pn_users))) table.

Now hows that for flexability? Now all you need to do is assign these arrays to '_objJoin?' in the class constructor like this...

---

Previous | Top | Next

---

CategoryDeveloperDocs