Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive /

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

LEFT JOIN query


Reply


1044 Views

Avatar
JGC

Community Member, 25 Posts

30 October 2008 at 3:57am

Hi all

I'm trying to get related data from two tables and output it into a TableListField which can then use the LightWindow to show more detailed information about rows and allow easy editing of them.

I've been trawling through the documentation for the last couple of hours and I can't find anything useful in there. The TableListField article is promising, but its JOIN parameter is empty >_<

Can anyone give me a hand, please?

   public function showCaseTable() {
      $resultSet = new DataObjectSet();
      $filter = '';
      $sort = "CaseTracker.ID ASC";
      $join = 'LEFT JOIN CaseTrackerStatus ON CaseTracker.Status = CaseTrackerStatus.ID';
      $instance = singleton('CaseTracker');      
      $query = $instance->buildSQL($filter, $sort, null, $join);
      $query->groupby[] = 'CaseTracker.ID';

      $trackerTable = new TableListField(
         'TrackerTable',
         'CaseTracker',
         array(
            'ID' => 'ID',
            'CaseName' => 'Name',
            'LastEdited' => 'Last updated',
            'CaseStatus' => 'Status'
         )
      );

      $trackerTable->setFieldCasting(array(
         'LastEdited' => 'Date->Nice'
      ));

      $trackerTable->setPermissions(array(
         'export',
         'delete',
         'print'
      ));

      // Generate fieldsets
      $fields = new FieldSet(
         new HiddenField('ID', 'ID'),
         $trackerTable
      );

      $actions = new FieldSet(
         new FormAction('save', 'Update Cases')
      );

      return new Form($this, "EditForm", $fields, $actions);

   }