Datatables editor and Views

Datatables editor and Views

aziegler3aziegler3 Posts: 47Questions: 11Answers: 1

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?

Answers

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421

    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 )
        )
    )
    
  • aziegler3aziegler3 Posts: 47Questions: 11Answers: 1
    edited October 9

    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' ) 
    
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

Sign In or Register to comment.