3 sql tables: mjoin?

carrarachristophe

Hello,

I have 3 sql tables:
1. portfolio
* id
* asset
* number
2. asset
* asset_id
* asset_name
3. asset_value
* id
* asset
* date
* value

I created a datatable in which I have the data contained in the first 2 sql tables by using:

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

My question is the following: how can I add to the datatable the data of the sql table asset_value? (for example to display the value of an asset at a certain date)
I had a look at the mjoin doc but am not sure it answers the question.

allan

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

->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

Thanks @rf1234 - I think you caught some nuances that I missed there! Happy New Year btw

carrarachristophe

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

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

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

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

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#