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

aziegler3

It took me a long time, but I figured out what the problem was:
Create and Edit use a sql query that includes RETURNING.
Oracle does not support insert statements with RETURNING clauses in views. That is where the problems is.

allan

That is correct. As I mentioned in my first reply:

you need to write back to an actual table

The example I linked to does that by specifying the VIEW for the readTable and the underlying table that is to be written to is the table specified in the constructor.

Allan

aziegler3

The problem is that those views are build with some GIS libraries from a GIS software product that strongly advices you against writing data to the tables. They insist that you write to the views.
Anyways, I found a work around (more like a hack) to post to the views with some custom code, and still get to use the good looking datatables, that are used across our website in different ways.

allan

Awesome - good to hear you've got a workaround.

Allan