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

Join or View - Neither work


Reply


3 Posts   1027 Views

Avatar
JGC

Community Member, 25 Posts

4 November 2008 at 11:51pm

Edited: 05/11/2008 12:03am

Hi all

I'm trying to select data for a query from two tables, and get it into a DataObject one way or another, so that it can then be used in a TableListField. I've spent a LOT of time trawling through documentation and code for the core files, and I can't get it to work.

I can do a DB::query to get the data, but then it's not in a DataObject, and it's not the best way of doing it, I know.

For views, do I need to create a DB table (like I do for proper tables) before it's queryable?

Any help would be much appreciated, it's driving me up the wall.

Thanks.

Current code:

# Either this
      $CT = DataObject::Get('casetrackerview');

# Or this
      $report = new TableListField(
         'CorporateReport',
         'CaseTracker',
         array(
            'ID' => 'ID'
         )
      );
# Is preferred for getting data, either way it needs to go into the TableListField

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

         $fields = new FieldSet(
            new HiddenField('ID', 'ID'),
            $report
         );

         $actions = new FieldSet();

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

DB Declarations:

class CaseTracker extends DataObject {
   static $db = array(
      'CaseName' => 'Varchar',
      'DateBirth' => 'Date',
      'TelNumber' => 'Int',
      'Address' => 'Text',
      'Postcode' => 'Varchar(8)'
   );

   static $has_one = array(
      'Status' => 'CaseTrackerStatus'
   );

class CaseTrackerStatus extends DataObject {
   static $db = array(
      'ShortText' => 'Varchar',
      'AllText' => 'Varchar(100)',
      'EmailData' => 'HTMLText'
   );

   static $has_many = array(
      'Statuses' => 'CaseTracker'
   );

}

Avatar
JGC

Community Member, 25 Posts

5 November 2008 at 11:05pm

Just to close off the topic, I got this working eventually.

      $sqlQuery = new SQLQuery();
      $sqlQuery->select = array('T1.C1, T1.C2, T1.C3, CONCAT(T1.C4, ". ", T2.C5) AS L1');
      $sqlQuery->from = array('T1INNER JOIN T2ON T1.C1= T2.C1');
      $sqlQuery->where = array();
      $sqlQuery->orderby = '';
      $sqlQuery->groupby = array();

      $allX = $sqlQuery->execute();
      $Xx= new DataObjectSet();
      foreach ($allX as $X) {   $Xx->push( new DataObject( $X));   }

      $XTLF= new TableListField(
         'Name',
         'T1',
         array(
            'C1' => 'H1',
            'C2' => 'H2',
            'C3' => 'H3'
         )
      );

      $XTLF->setCustomSourceItems($Xx);

Avatar
JGC

Community Member, 25 Posts

11 November 2008 at 3:40am

Just adding something else on this for anyone who may stumble on this later - I think I found the proper way of joining.

Posted a comment on the tutorial for creating modules: http://doc.silverstripe.com/doku.php?id=private:tutorial:creating-a-module&disqus_reply=3662588#comment-3662588