self join alias not working

mmontoya

What is wrong with the code below. The 'Referred by' column is blank.

Editor::inst( $db, 'Customers', 'CustomerID' )
    ->fields(
        Field::inst( 'Customers.FirstName' )
                ->validator( 'Validate::notEmpty' ),
        Field::inst( 'Customers.LastName' )
                ->validator( 'Validate::notEmpty' ),
        Field::inst( 'Customers.City'),
        Field::inst( 'Customers.CustomerID'),
        Field::inst( 'refBy.CustomerID'),
        Field::inst( 'Customers.ReferredByID')
            ->options( function () use ( $db ) {
                // Use `selectDistinct` to get the full list of names from the
                // database and then concatenate the first and last names
                $userList = $db->selectDistinct( 'Customers', 'CustomerID, FirstName, LastName', null, 'LastName ASC' );
                $out = array();
 
                while ( $row = $userList->fetch() ) {
                    $out[] = array(
                        "value" => $row['CustomerID'],
                        "label" => $row['FirstName'].' '.$row['LastName']
                    );
                }
 
                return $out;
            } ),        
        Field::inst( 'Customers.CustomerStatus')
    )
    ->leftJoin('Customers as refBy','Customers.ReferredByID','=','refBy.CustomerID')
    ->process( $_POST )
    ->json();

          var table = $('#table').DataTable( {
              //jQueryUI: "true",
              //dom: "Tfrtp",
              lengthChange: false,
              autowidth: "false",
              ajax: "lib/getSearch.php",
              columns: [
                    {data: "Customers.CustomerID",
                      visible: false
                    },
                    {data: "Customers.FirstName"},
                    {data: "Customers.LastName"},
                    {data: "Customers.CustomerStatus",
                         render: function (val, type, row) {
                                      return val == 0 ? "Referral" : "Customer";}
                    },
                    {
                          data: "Customers.ReferredByID",
                          render: function ( val, type, row ) {
                              return val.FirstName ?
                                  val.FirstName +' '+ val.LastName :
                                  '';
                          },
                          defaultContent: ""
                    },                    
                    {data: "Customers.City"}
              ]   
          } );

mmontoya

never mind. I see I forgot to include the refBy FirstName LastName in the data source:

        Field::inst( 'refBy.LastName'),
        Field::inst( 'refBy.FirstName')

It works now.