Multiple joins to one table

andyhclark

Hi Allan

Having a bit of a problem getting my head around the join example and how I could have multiple joins to the same table. Lets say I have a table of employees (hub_nps) and attached to them is a comments table (hub_nps_comments). The comments table has a comment_type column that relates to the type of comment. Each employee can have to 4 different type of comment attached.

I'm using an ajax json source for the table data and only one type of comment is displayed in the table. At the moment I have a single join specified like this:

[code]
Editor::inst( $db, 'hub_nps')
->fields(
Field::inst( 'id' ),
Field::inst( 'date_visit' )
->validator( 'Validate::dateFormat_required', 'd/m/y' )
->getFormatter( 'Format::date_sql_to_format', 'd/m/y' )
->setFormatter( 'Format::date_format_to_sql', 'd/m/y' ),
Field::inst( 'employee' )
->validator( 'Validate::maxLen_required', 100 ),
Field::inst( 'location' )
->validator( 'Validate::required' ),
Field::inst( 'score' )
->validator( 'Validate::required' )
)
->join(
Join::inst( 'hub_nps_comments', 'object' )
->join( 'id', 'parent_id' )
->fields(
Field::inst( 'comment' ),
Field::inst( 'comment_type' )
)
)
->process( $_POST )
->json();
[/code]

Is there some method of aliasing the comments table so I can join to it again?

Thanks

Andy

allan

Hi Andy,

If I understand correctly, you want to get all comments based on the parent_id - is that correct? In which case, you could just use an array join rather than object join (i.e. `Join::inst( 'hub_nps_comments', 'array' )` ).

Remember to be sure to post an array back if you are using it to edit data though since Editor will modify the joined table and would delete any rows which no longer match! (its definitely a good idea to make sure you have a backup of the database, just in case...)

Allan