How to join a table on two conditions?

dylan.pattenaude

I see that the leftJoin function takes the four parameters:

leftJoin( 'view_data_points', 'data_reporting_manual.data_point_id', '=', 'view_data_points.data_point_id' )

This can produce something like:

INNER JOIN data_reporting_manual ON data_reporting_manual.data_point_id = view_data_points.data_point_id

However what I'm trying to do is that last JOIN with the AND. How can I do this?

SELECT
view_data_points.short_name,
view_data_points.data_point_id,
data_reporting_manual.data_date,
data_reporting_manual.data_value,
data_reporting.data_value as SFValue
FROM
view_data_points
INNER JOIN data_reporting_manual ON data_reporting_manual.data_point_id = view_data_points.data_point_id
INNER JOIN data_reporting ON data_reporting_manual.data_point_id = data_reporting.data_point_id AND data_reporting_manual.data_date = data_reporting.data_date

What I've tried is the following code in my ajax.php. Notice the comment //breaks to see the join that breaks things:

Editor::inst( $db, 'data_reporting_manual' )
      ->pkey( 'data_reporting_manual.data_id' )
      ->field(
          Field::inst( 'data_reporting_manual.data_date' )->validator( 'Validate::dateFormat', array(
              "format"  => Format::DATE_ISO_8601,
              "message" => "Please enter a date in the format yyyy-mm-dd"
          ) )
               ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
               ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
          Field::inst( 'data_reporting_manual.data_value' ),
          Field::inst( 'view_data_points.short_name' ),
          Field::inst( 'data_reporting_manual.data_point_id' )
               ->options( 'view_data_points', 'data_point_id', 'short_name' )
      )
      ->leftJoin( 'view_data_points', 'data_reporting_manual.data_point_id', '=', 'view_data_points.data_point_id' )
    //breaks  
      ->join(
        Editor\Join::inst( 'data_reporting', 'object' )->link( 'data_reporting_manual.data_point_id', 'data_reporting.data_point_id' )->link( 'data_reporting_manual.data_date', 'data_reporting.data_date' )->field(
            Field::inst( 'data_reporting.data_value' )->set( false )->name( 'sf_data_value' )
        )
    )
      ->process( $_POST )
      ->json();

allan

Hi,

There currently isn't a well defined why of doing what you are looking for with the PHP classes - however, it is actually possible with a little bit of a workaround. It is discussed in this thread.

Regards,
Allan