Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Join or View - Neither work

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

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

Page: 1
Go to End
Author Topic: 994 Views
  • JGC
    Avatar
    Community Member
    25 Posts

    Join or View - Neither work Link to this post

    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'
       );

    }

  • JGC
    Avatar
    Community Member
    25 Posts

    Re: Join or View - Neither work Link to this post

    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);

  • JGC
    Avatar
    Community Member
    25 Posts

    Re: Join or View - Neither work Link to this post

    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

    994 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.