Datatables editor and Views

aziegler3

I am asking the question, although I have the feeling that I already know the answer:
Can I use database views (relational views) instead of just tables?
My guess is that the first hurtle is that views have no indexes and datatables, seems to me, needs the indexes.

Did anybody tried this before?

allan

Sort of. You can use the VIEW for the getting of data, but you need to write back to an actual table - there is an example of that here.

The lack of index isn't an issue, but you do need to be able to uniquely identify a row (normally a primary key index, but it could be something else).

Allan

rf1234

but you need to write back to an actual table

Bearing that in mind, here is an example using a view. I need to Mjoin a table with itself. For that you need a view. The table I use is called "contract" and the view on "contract" is called "underlying". The link table between the two which is called "derivative_has_underlying" must be a real table, not a view.

//single derivative contracts need to have underlying contracts assigned to them
//without an assignment of an underlying they may not become approved.
//the array of the underlyings is displayed in the data table
->join(
Mjoin::inst( 'underlying' )
    ->link( 'contract.id', 'derivative_has_underlying.derivative_contract_id' )
    ->link( 'underlying.id', 'derivative_has_underlying.underlying_contract_id' )
    ->order( 'serial, instrument, type asc' )
    ->fields(
        Field::inst( 'id' )->set( false )
            ->options( Options::inst()
                ->table( 'underlying' )
                ->value( 'id' )
                ->label( array('serial', 'instrument', 'type', 'number') )
            //render serial, instrument, type, number
                ->render( function ( $row ) {               
                    return '# '.$row['serial']
                            .' / '.renderInstrument($row['instrument'])
                            .' / '.renderTypeDerivative($row['type'])
                            .' ('.$row['number'].')';
                } )
                ->order( 'serial, instrument, type asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                    $q ->where( function($r) {
                        $r ->where('govdept_id', $_SESSION['govdept_id'] );                         
                        $r ->where('instrument', 'W', '<' );
                    });
                } )
            ),
        Field::inst( 'serial' )->set( false ),
        Field::inst( 'instrument' )->set( false ),    
        Field::inst( 'type' )->set( false ),
        Field::inst( 'number' )->set( false )
    )
)

aziegler3

Allan, the example that you shared is great.
Shame on me because I saw it before but I did not pay enough attention to the details. So 'staff_newyork' is the view where you read and 'users' is the table where you write.
I will give it a shot and report back here.
Now, in this example, you never state what the unique identifier is. I assume you can always use

Editor::inst( $db, 'table name', 'unique identifier' ) 

allan

Yes. I think you'll need to make sure the name is the name for the primary key column and that matching value in the VIEW.

Allan