Answers

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  January 2 Answer ✓

  Is asset_value an Mjoin link table, or do you want to left join from your Mjoin-ed table? Could you show me your full PHP perhaps?

  Thanks,
  Allan
• rf1234 Posts: 2,809Questions: 85Answers: 406

  January 2 Answer ✓

  ->leftJoin( 'asset', 'asset.asset_id', '=', 'portfolio.asset' )
  ->leftJoin( 'asset_value', 'asset_value.asset', '=', 'asset.asset_id' )
  

  assuming that "asset_value.asset" contains the "asset_id"

  "Mjoin" doesn't mean you are joining multiple tables. It only means that the output of the join can be multiple records. Joining multiple tables is done using the leftJoin method - like in SQL. If you would like to do an INNER JOIN you would need to use the leftJoin method and exclude NULL values through the WHERE clause.

  Here is something from my own coding. Multiple LEFT JOINS, some should be INNER JOINS. Hence the NOT NULL checks.

  ->leftJoin( 'contract_has_creditor_approval', 'contract.id', '=', 'contract_has_creditor_approval.contract_id')
  ->leftJoin( 'contract_has_govdept_approval', 'contract.id', '=', 'contract_has_govdept_approval.contract_id')
  ->leftJoin( 'contract_after_bidtime_end', 'contract.id', '=', 'contract_after_bidtime_end.contract_id')  
  ->leftJoin( 'contract_exp_date', 'contract.id', '=', 'contract_exp_date.contract_id')
  ->leftJoin( 'contract_exp_date_no_estimated_rates', 'contract.id', '=', 'contract_exp_date_no_estimated_rates.contract_id')
  ->where( function($q) {            
      $q ->where( 'contract.govdept_id', $_SESSION['govdept_id'] );
  //for the inbox we may only show contracts with creditor approval that are
  //not manual contracts
      if ( isset($_SESSION['inboxContractPage']) ) {
          $q ->where( 'contract_has_creditor_approval.contract_id', null, '!=' );
          $q ->where( 'contract_after_bidtime_end.contract_id', null, '!=' );
          $q ->where( 'contract.gov_manual_creditor_id', null ); 
      }
  //for the other two pages we need govdept approval as well
  // (contracts with govdept approval also have creditor approval
  // OR a change was initiated which should still make them show up on the contract page
      if ( isset($_SESSION['contractPage']) ) {
          $q ->where( 'contract_has_govdept_approval.contract_id', null, '!=' );
      }
  //  if a change was initiated they might not have creditor approval and then
  //  we don't want to see them in the expirations!!
      if ( isset($_SESSION['inboxExpPage']) ) {
          $q ->where( 'contract_has_creditor_approval.contract_id', null, '!=' );
          $q ->where( 'contract_has_govdept_approval.contract_id', null, '!=' );
      }
  })
  
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  January 2

  Thanks @rf1234 - I think you caught some nuances that I missed there! Happy New Year btw
• carrarachristophe Posts: 100Questions: 24Answers: 2

  January 4

  Thank you both, I don't know why i wanted to complicate, another simple leftJoin is sufficient indeed...
  But it displays a line for each and every asset_value.date.
  Do you know how I could get a single line for each asset. asset_id where asset_value.date is max for this asset. asset_id?
• rf1234 Posts: 2,809Questions: 85Answers: 406

  January 4

  You would do that with a subselect just like in SQL

  Here is a simple example
  https://editor.datatables.net/manual/php/conditions#Sub-selects
• rf1234 Posts: 2,809Questions: 85Answers: 406

  January 4

  Something like this could work. What you would need is a so-called correlated subquery. "asset.asset_id" is the "outer" value of the asset_id while "a.asset" is the "inner" value of the asset_id to be correlated.

  ->where( function ( $q ) {
      $q  ->where( 'asset_value.date',  
          '( SELECT MAX(a.date)    
              FROM asset_value a 
              WHERE asset.asset_id = a.asset
              LIMIT 1
              )', '=', false);
  } ) 
  

  Not sure whether this will work. Here is more on correlated subqueries:
  https://www.geeksforgeeks.org/sql-correlated-subqueries/

  This probably will only work if you have FIELD instances for "asset.asset_id" and "asset_value.date" in your PHP Editor.
• carrarachristophe Posts: 100Questions: 24Answers: 2

  January 6

  Thanks very much.

  I first failed to use your code because I used it for a specific field:

  Field::inst( 'asset_value.value' )
      ->options( Options::inst()
          ->where( function ( $q ) {
              $q  ->where( 'asset_value.date',
              '( SELECT MAX(a.date)   
              FROM asset_value a
              WHERE asset.asset_id = a.asset
              LIMIT 1
              )', '=', false);
  } ))
  

  But when I use it between the list of fields and the leftJoins, no more error message.

  The only problem is that:
  * instead of displaying all the assets with their respective value at their max date
  * the datatable now displays the asset(s) with their respective value at the max date (of all the assets)

  Any idea how I could amend the code accordingly?
• rf1234 Posts: 2,809Questions: 85Answers: 406

  January 7 edited January 7

  That sounds like the correlated subquery doesn't work: the outer repective "asset.asset_id" doesn't get passed into the query.

  Then I would choose a completley different path here. I would get rid of the leftJoin with the table that causes the multiple rows, alias one of the fields of the primary table and return the value you need using SQL and Editor's "raw" method.

  Here is an example from my own coding:

  Field::inst( 'vat_subcategory.id AS vat_subcategory.questions' )->set( false )
      ->getFormatter( function ( $val, $data, $opts ) use ( $db ) {
          $result = $db->raw()
              ->bind( ':fk',      $val )
              ->exec( 'SELECT COUNT(*) AS questionCount 
                         FROM vat_question  
                        WHERE vat_subcategory_id = :fk' );
          $row = $result->fetch(PDO::FETCH_ASSOC);
          return $row["questionCount"];
      } ),
  

  "vat_subcategory" is the primary table in this example. I am not leftJoining "vat_question" at all.

  All I do is create an "alias" of "vat_subcategory.id", call it "vat_subcategory.questions" and hence pass "vat_subcategory.id" into the getFormatter. Then I select whatever I need and return it from the getFormatter. Done. Of course this is read only. Hence "->set(false)". I do stuff like this all the time, otherwise Editor isn't flexible enough for my purposes.

  This post is on the various methods to use SQL with Editor's db handler.
  https://datatables.net/forums/discussion/comment/227063#