Show field from Link Table in MJoin (PHP)

jasoncaditrack

I am asking a similar question to https://datatables.net/forums/discussion/56510, but the proposed solution in the answer given is not sufficiently detailed for me to understand.

My challenge is to display in the Datatable one field (an attribute) from a Mjoin's 'Link Table'.

I have 3 tables in the MJoin:
1. twork_src - Parent Table, ("Work Orders")
2. tlots - Link Table ("Lots")
3. titems - Child Table ("Items")

I want to show on the Datatable ( but not Edit) tlots.lot_alpha. Until I tried to add this field to the PHP script, Editor worked fine!

I cannot figure out the correct PHP script from the response in quesiton 56510.

Here is my latest PHP script. When I introduced line 11 , Field::inst( 'tlots.lot_alpha' ) it caused the error
An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tlots.lot_alpha' in 'field list'

FYI there is a 1:1 relationship between tlots and titems.

Thanks in advance for any suggestions!

  Editor::inst( $db, 'twork_src' )
    ->fields(
     Field::inst( 'twork_src.wo_num' ),
     Field::inst( 'twork_src.status' ),
     Field::inst( 'twork_src.src_date' ),
     Field::inst( 'twork_src.src_number' ),
     Field::inst( 'twork_src.src_reference' ),
     Field::inst( 'twork_src.src_comment' ),
     Field::inst( 'twork_src.src_type' ),
     Field::inst( 'tstaff.staff_name' ),
     Field::inst( 'tlots.lot_alpha' )

    )
    ->join(
        Mjoin::inst( 'titems' )
            ->link( 'twork_src.id' , 'tlots.lot_src_id' )
            ->link( 'titems.id' , 'tlots.item_id' )
            ->fields(
              Field::inst( 'item_name' ),
              Field::inst( 'item_num' )
            )
    )
    ->leftJoin( 'tstaff', 'tstaff.id', '=', 'twork_src.staff_id' )

    ->  where( function ( $q ) {
      $q->where( function ( $r ) {
         $r->where(  'twork_src.src_type' , 'RX' );
      } );
    } )
  ->process( $_POST )
  ->json();

jasoncaditrack

CORRECTION: FYI there is a many:1 relationship between tlots and titems.

allan

Hi Jason,

First thing I would say is that I would strongly discourage you from having anything other than just to two primary keys in the link table if you want to be able to edit the assignments. The way Editor's Mjoin works is to delete all matching rows from the host's primary key, and then insert the new data when editing. Thus any other information on the link table would be lost. You can add ->set(false) to the Mjoin to stop it from being writable.

Now, that said. We still don't have a left join on the Mjoin I'm sorry to say - I really need to add that! I don't think the left join on the parent is going to work for you in this case.

Instead, what I would recommend you do is to create a VIEW in place of titems (so you would do Mjoin::inst('titems_view'), or whatever you call it) and that VIEW would select from titems and left join to the link table.

That will make things fall into place much quicker.

Allan

jasoncaditrack

Hi Allan
Thank you so much for your answer. Although the left join functionality would have been handy, your recommendation was a good one.

On reflection, there are several good reasons to create a view of these 3 tables, I realize that I had started to implement some unnecessarily complex SQL elsewhere in my code, and the View Solution kills a couple of birds.
Thanks again!
Jason