Unable to Perform one to many join

Martyn.s

Link to test case:
Debugger code (debug.datatables.net):
->join(
Mjoin::inst('workSites')
//->name('w')
->link('a.announcementID','announcementsToWorkSites.announcementID')
->link('workSites.workSiteID','announcementsToWorkSites.workSiteID')
->fields(
Field::inst('locationName')
)
)
Error messages shown:
DataTables warning: table id=announcementsListing - Join was performed on the field 'announcementID' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.

Description of problem:
Trying to execute a one to many join for an announcement table using data tables, but it refuses to work. Any idea why?

rf1234

Well, the error message says it clearly: You need to include the field "a.announcementID" in the field list of the Editor containing the MJoin. Please use Markdown as described below to highlight your code.

Editor::inst( $db, 'a' ) 
->field(
    Field::inst( 'a.announcementID' )->set( false )
)
->join(
Mjoin::inst('workSites')
    //->name('w')
    ->link('a.announcementID','announcementsToWorkSites.announcementID')
    ->link('workSites.workSiteID','announcementsToWorkSites.workSiteID')
    ->fields(
        Field::inst('locationName')
    )
)

Martyn.s

Sorry about that, I excluded the full code and forgot to do the markdown

Editor::inst( $db, 'announcements AS a', 'announcementID' )
    ->field(
        Field::inst( 'a.announcementID' )->set(false), // ID is automatically set by the database on create
        Field::inst( 'a.announcerID' ),
        /*Field::inst( 'a.workSiteID' )->options( Options::inst()
      ->table( 'workSites' )
      ->value( 'id' )
      ->label( 'location' )
      ->order('location')
    )->setFormatter( function ( $val, $data, $opts ) {
      return ($val=="") ? null : (int)$val;
    } ),*/
        Field::inst( 'a.announcementTitle' )->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A Title is required' )  
            ) ),
        Field::inst( 'a.announcementDescription' )->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A Description is required' )    
            ) ),
        Field::inst( 'a.isGlobal' ),
        Field::inst( 'a.announcementTypeID' )->options( Options::inst()
      ->table( 'announcementType' )
      ->value( 'announcementTypeID' )
      ->label( 'announcementType' )
      ->order('announcementType')
    )->validator( Validate::dbValues() ),
    
    Field::inst( 'a.announcementStatusID')->options( Options::inst()
      ->table( 'announcementStatus' )
      ->value( 'announcementStatusID' )
      ->label( 'statusName' )
      ->order('statusName')
    )->validator( Validate::dbValues() ),
        Field::inst( 'a.isExternalLink' ),
        Field::inst( 'a.link' ),
        Field::inst( 'a.announcementStartDate' )
          ->validator( 
            Validate::dateFormat('Y/m/d',ValidateOptions::inst()->allowEmpty( false)),
            Validate::notEmpty( ValidateOptions::inst()
                  ->message( 'A Start Date is required' )   
              )
          )
            ->getFormatter( Format::datetime( 'Y-m-d','Y/m/d' ) )
            ->setFormatter( Format::datetime( 'Y/m/d','Y-m-d' ) ),
        Field::inst( 'a.announcementEndDate' )
          ->validator( 
            Validate::dateFormat('Y/m/d',ValidateOptions::inst()->allowEmpty( false)),
            Validate::notEmpty( ValidateOptions::inst()
                  ->message( 'A End Date is required' ) 
              )
          )
            ->getFormatter( Format::datetime( 'Y-m-d','Y/m/d' ) )
            ->setFormatter( Format::datetime( 'Y/m/d','Y-m-d' ) ),
        Field::inst( 'a.announcementDeadline' )
          ->validator( 
            Validate::dateFormat('Y/m/d',ValidateOptions::inst()->allowEmpty( true))
          )
            ->getFormatter( Format::datetime( 'Y-m-d','Y/m/d' ) )
            ->setFormatter( Format::datetime( 'Y/m/d','Y-m-d' ) ),
        Field::inst( 'a.announcementRepeatID' )->options( Options::inst()
      ->table( 'announcementRepeat' )
      ->value( 'announcementRepeatID' )
      ->label( 'repeatName' )
      ->order('repeatName')
    )->validator( Validate::dbValues() ),
        Field::inst( 'a.announcementEndOfRepeat' )
          ->validator( 
            Validate::dateFormat('Y/m/d',ValidateOptions::inst()->allowEmpty( true))
          )
            ->getFormatter( Format::datetime( 'Y-m-d','Y/m/d' ) )
            ->setFormatter( Format::datetime( 'Y/m/d','Y-m-d' ) ),
        //Field::inst( 'w.location' ),
        Field::inst( 't.announcementType' ),
        Field::inst( 'r.repeatVal' ),
        Field::inst( 'r.repeatUnit' ),
        Field::inst( 'r.repeatName' ),
        Field::inst( 'r.repeatDisplay' ),
        Field::inst( 's.statusName' ),
        Field::inst( 'a.isDeleted' )->setFormatter( function ( $val, $data, $opts ) {
      return ((int)$val!=1 && (int)$val!=0) ? 0 : (int)$val;
    } ),
        Field::inst( 'a.deletedByID' )->setFormatter( function ( $val, $data, $opts ) {
      return ($val=="") ? null : (int)$val;
    } ),
        Field::inst( 'a.dateDeleted' )
          ->validator( 
            Validate::dateFormat('Y/m/d',ValidateOptions::inst()->allowEmpty( true))
          )
            ->getFormatter( Format::datetime( 'Y-m-d','Y/m/d' ) )
            ->setFormatter( Format::datetime( 'Y/m/d','Y-m-d' ) )
  )
  //-> leftJoin('announcementsToStaff as ats', 'ats.announcementID', '=', 'a.announcementID')
  -> leftJoin('announcementType as t', 't.announcementTypeID', '=', 'a.announcementTypeID')
  -> leftJoin('announcementRepeat as r', 'r.announcementRepeatID', '=', 'a.announcementRepeatID')
  -> leftJoin('announcementStatus as s', 'a.announcementStatusID', '=', 's.announcementStatusID')
  -> leftJoin('staff as admin', 'admin.staffID', '=', 'a.announcerID')
  ->join(
      Mjoin::inst('workSites')
        //->name('w')
        ->link('a.announcementID','announcementsToWorkSites.announcementID')
        ->link('workSites.workSiteID','announcementsToWorkSites.workSiteID')
        ->fields(
          Field::inst('locationName')
        )
      )

As you can see, a.announcementID is already included, which is what has me so confused as to why it 'throwing that error

My apologies for not having the clearest first initial post

rf1234

Mmmm ... weird!

I have this is in my code - and it has been working for years now:

Editor::inst( $db, 'user' )
->field(
    Field::inst( 'user.id' )->set( false )
............
//    show user roles        
->join(
    Mjoin::inst( 'govdept' )
        ->link( 'user.id', 'govdept_has_user.user_id' )
        ->link( 'govdept.id', 'govdept_has_user.govdept_id' )
        ->order( 'govdept.name asc' )
        ->fields(
            Field::inst( 'govdept.name AS deptName' )->set( false ),
            Field::inst( 'govdept_has_user.role AS userRole' )->set( false )
        )
    )
->join(
    Mjoin::inst( 'creditor' )
        ->link( 'user.id', 'creditor_has_user.user_id' )
        ->link( 'creditor.id', 'creditor_has_user.creditor_id' )
        ->order( 'creditor.name asc' )
        ->fields(
            Field::inst( 'creditor.name AS deptName' )->set( false ),
            Field::inst( 'creditor_has_user.role AS userRole' )->set( false )
        )
    )
->join(
    Mjoin::inst( 'lgf' )
        ->link( 'user.id', 'lgf.user_id' )
        ->fields(
            Field::inst( 'lgf.role AS userRole' )->set( false )
        )
    )

The only differences I can see is that
- I don't have my primary table aliased
- My id fields are called "id"

The first difference might cause the issue. Maybe Mjoin doesn't "like" aliased table names?!

Apart from that maybe @allan has an idea?

allan

I'm reasonably sure it is going to be the alias that is causing the issue. If you replace 'announcements AS a' with 'announcements' and update the various a references, I suspect it will work immediately.

I'll take more of a look into this.

Allan

Martyn.s

Thank you!

I'll remove the alias for my table for now then and see how it works while waiting for the results of you looking into it