trying to understand example

trying to understand example

ashiersashiers Posts: 101Questions: 8Answers: 7
edited June 2013 in Editor
Hi there,

I'm going over the example at: http://editor.datatables.net/release/DataTables/extras/Editor/examples/joinSelf.html

I'm trying to understand this business of a self referencing table. By looking over your PHP code below:
[code]
$editor = Editor::inst( $db, 'users' )
->field(
Field::inst( 'id' )->set( false ),
Field::inst( 'first_name' )->validator( 'Validate::required' ),
Field::inst( 'last_name' )->validator( 'Validate::required' ),
Field::inst( 'manager' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'users', 'object' ) // Read from 'users' table
->aliasParentTable( 'manager' ) // i.e. FROM users as manager
->name( 'manager' ) // JSON / POST field
->join( 'id', 'manager' ) // Join parent `id`, to child `manager`
->set( false ) // Used for read-only (change the 'manager' on the parent to change the value)
->field(
Field::inst( 'manager.first_name', 'first_name' ),
Field::inst( 'manager.last_name', 'last_name' )
)
);
[/code]

am I to understand that the table named users is constructed like this?

users

id | first_name | last_name | manager
|________< references <______|

whereby manager is of type int and references the id values?

For instance, lets say we have three records in table users. Two people are managers: Patrick and Stacey. Alan would reference Patrick as his manager like so:

id | first_name | last_name | manager

32 | Alan | Shiers | 43
43 | Patrick | Duffy |
44 | Stacey | MacDonald |

So if I'm editing the record for Alan in an Edit form only the manager reference number for Alan gets changed from 43 to 44, if Stacey suddenly became his new manager.

Have I got this right?

Also, how are you flagging those who are managers in the users table? Do you have another field of type boolean named isManager, like this:

id | first_name | last_name | manager | isManager

32 | Alan | Shiers | 43 | false
43 | Patrick | Duffy | | true
44 | Stacey | MacDonald | | true

I'm looking at the other piece of PHP code and trying to figure out how you are able to discern who is a manager and who isn't:

[code]
if ( !isset($_POST['action']) ) {
$userList = $db->select( 'users', 'id, first_name, last_name' );

$out['userList'] = array();
while ( $row = $userList->fetch() ) {
$out['userList'][] = array(
"value" => $row['id'],
"label" => $row['id'].' '.$row['first_name'].' '.$row['last_name']
);
}
}
[/code]

This is evidently returning an array of managers while concatenating their first name and last name. But I don't see where you are telling the database to query for only managers???? How does it know? I want to try to duplicate this example here on my end, but I'm going to have to make some changes to my employees table on my database in order to make this work I think.

Please advise,

Alan

Replies

  • allanallan Posts: 63,160Questions: 1Answers: 10,406 Site admin
    > So if I'm editing the record for Alan in an Edit form only the manager reference number for Alan gets changed from 43 to 44, if Stacey suddenly became his new manager.

    Yes spot on, with one technicality. All of the fields will be written in the row on an update form Editor, Generally this will have no effect, but if you had database triggers on those columns, they would be activated.

    > Also, how are you flagging those who are managers in the users table? Do you have another field of type boolean named isManager

    No, but it would be possible to do something like that. There is nothing in the table to say who is a manger and who isn't as it wasn't needed in the demo schema, but I can see that it might be useful in a real use case.

    In the example, anyone can be a manager.

    > I'm going to have to make some changes to my employees table on my database in order to make this work I think.

    I would strongly encourage that! The demo schema is just that - suitable for the demo only! It was designed to show some of the features of Editor, not for real world usage. I would always encourage you to create the schema that you need, and then have Editor conform to that. Editor should never try to shape what you want - it should fit in!

    Allan
  • ashiersashiers Posts: 101Questions: 8Answers: 7
    OK, cool. I'll start working on this scenario tomorrow. I'll let you know how it goes.

    Alan
  • ashiersashiers Posts: 101Questions: 8Answers: 7
    Well now, that was really peculiar. I got things working in order to support this scenario, but it required additional Java methods and programming gymnastics to make it all happen. I'm hoping this is as difficult as it gets. Please say it is so. :-)

    Alan
This discussion has been closed.