Answers

• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  October 8

  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 Posts: 2,991Questions: 87Answers: 421

  October 9

  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 Posts: 47Questions: 11Answers: 1

  October 9 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' ) 
  
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  October 9

  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