one to many relationship problem

crush123

I have 2 tables,

tblpatron, tblitem in a one to many relationship

Using the one to many example, http://editor.datatables.net/manual/php/array-joins (scenario 3) i want to show an array of items associated with each patron.

I have an instance of my editor impementation below, but I always receive an error

"sError":"Table selected fields (i.e. '{table}.{column}') in Join must be read only. Use set(false) for the field to disable writing."}

I have tried adding ->set( Field::SET_NONE ) to every single field, and i still get the error.

Editor::inst( $db, 'tblpatron', 'PatronID' )//table name and PKey(defaults to ID)
->field(
    Field::inst( 'tblpatron.FirstName' ),
    Field::inst( 'tblpatron.LastName' ),
    Field::inst( 'tblpatron.EmailAddress' ),
    Field::inst( 'tblpatron.Phone' )
    )   
->join(
    Join::inst( 'tblitem', 'array' )
        ->join( 'tblpatron.PatronID', 'tblitem.ItemPatronID')
        ->fields(               
                Field::inst( 'tblitem.ItemPatronID' )
                ->validator( 'Validate::required' )
        )
    )
        //->where('tblitem.EmailReceipt', '0', '=')
->process($_POST)
->json();

allan

I'm afraid you've found a bug in Editor 1.4.0 there.

In the Join.php file could you replace:

if ( $field->set() && $this->_set ) {

with:

if ( $field->set() !== Field::SET_NONE && $this->_set ) {

and that should address the problem. I'll have this in the next release.

Regards,
Allan

crush123

Thanks Allan.

Can you show me the syntax for setting a name alias via this method,

I am now getting ...

{"sError":"Table selected fields (i.e. '{table}.{column}') in `Join` must have a name alias which does not contain a period ('.'). Use name('---') to set a name for the field"}

allan

Rather than setting a name I would suggest just using:

Field::inst( 'ItemPatronID' )

for the field in your join instance.

It will automatically be nested under a tblitem object by the Join class in the returned JSON.

Allan

crush123

Ok, it seems to work, as long as i omit the table name from the Join::inst( ... ) as well

Join::inst( 'tblitem', 'array' )
        ->join( 'PatronID', 'ItemPatronID')