[Silverstripe 2.4]:如何从一对多关系中获取包含列的记录?

问题描述

有两个具有一对多关系的数据对象类"A"和"B".我想从父数据对象和子数据对象中获取包含列的数据.如何在Silverstripe的ORM或SQL查询中实现这一点.

there are two dataobject classes "A" and "B" having one-to-many relationship. I want to get data containing columns from both parent and child dataobject. How to achieve this in Silverstripe 's ORM or SQL Query.

示例:数据对象"A"具有两个事件，第一个事件包含一个日期信息(开始和结束日期).第二个事件包含两个日期信息(开始和结束日期).

Example: Dataobject "A" has two events and first event contain one date info(start and end date). Second event contains two dates info (start and end date).

我想查询数据库以获取包含来自"A"和"B"的列的信息，结果应显示3行.一个来自"A"，两个来自"B".

I want to query database to get information containing columns from both "A" and "B" and it should shows 3 row as result. one from "A" and two from "B".

我正在使用Silverstripe 2.4.电影可以显示多个日期.电影数据对象与MovieDate数据对象具有一对多关系.我想从Movie中获取所有列，并重复与它关联的MovieDate的每个记录.这意味着如果电影有两个日期，那么我想获得两个记录

I am using Silverstripe 2.4. A Movie Can have multiple dates to show. Movie dataobject has one-to-many relationship with MovieDate dataobject. I want to get all columns from Movie repeating each record of MovieDate associated with it. That means if movie has two dates, then I want to get two records

Title   Desc   StartDate   EndDate
-----   ----   ---------   -------
Matrix  AAA    2012-09-20  2012-09-20
Matrix  AAA    2012-09-29  2012-09-29

以下是出于您的考虑而编写的代码

Here is code for your kind consideration

<?php
class Movie extends DataObject
{
 public static $db = array(
    'Title'    => 'Varchar',
    'Desc'     => 'Text',
 );

 public static $defaults = array(
      'RedirectionType' => 'Internal',
 );

 public static $has_one = array(
      'Image'         => 'Image',
      'Parent'        => 'Page',
      "LinkTo"        => "SiteTree"
 );

public static $has_many = array(
      'MovieDates'     => 'MovieDate'
 );

 static $summary_fields = array(
    'Title'      => 'Movie Title',
    'Desc'       => 'Movie Description'
 );

 function getRequirementsForPopup() {

      Requirements::customCSS("
        .iframe_wrap {
                top: 35%;
            }
      ");

 }

 public function getCMSFields()
 {
    $fields = new FieldSet(new TabSet('Root', $tab1 =  new Tab('Main')));

    $fields->addFieldsToTab('Root.Main', new TextField('Title', 'Movie Title'));
    $fields->addFieldsToTab('Root.Main', new TextareaField('Desc', 'Movie Description'));

    $tablefield = new DataObjectManager(
        $this,
        'MovieDates',
        'MovieDate',
        array('MovieStartDate' => 'Movie Start', 'MovieEndDate' => 'Movie End')
     );


    $tablefield->setPopupWidth(900);
    $tablefield->setAddTitle("Movie Date/Time");
    $fields->addFieldsToTab('Root.Main', $tablefield);
    $fields->addFieldsToTab('Root.Main', new LiteralField("Space", "</br></br></br></br></br>") );
    return $fields;
 }

}

    <?php
class MovieDate extends DataObject{
static $db = array(
    'MovieStartDate'             => 'Datetime',
    'MovieEndDate'               => 'Datetime',
);

static $has_one = array(
    'Movie' => 'Movie'
);

function getCMSFields(){

        $fields = new FieldSet();

        $movieStartDate = new DateField('MovieStartDate', 'Movie Start');
        $movieStartDate->setConfig('showcalendar', true);
        $fields->push($movieStartDate);

        $movieEndDate = new DateField('MovieEndDate', 'Movie End');
        $movieEndDate->setConfig('showcalendar', true);
        $fields->push($movieEndDate);

        $space = new LiteralField("Space", "</br></br></br></br></br>");
        $fields->push($space);

    return $fields;
}

}

我认为

推荐答案

您追求的是左联接".不幸的是，silverstripe无法使用内置的ORM查询来检索已联接表上的数据(请参见 http://doc.silverstripe.org/framework/en/2.4/topics/datamodel#joining )

what you're after is a 'left join', i think.unfortunately, silverstripe has no means of retrieving data on a joined table using the built-in ORM queries (see http://doc.silverstripe.org/framework/en/2.4/topics/datamodel#joining)

所以您需要在此处进行简单的sql查询:

so you need to go with a plain sql query here:

    $query = "
        SELECT `Movie`.*, `MovieDate`.*
        FROM `Movie`
        LEFT JOIN `MovieDate` ON `Movie`.`ID` = `MovieDate`.`MovieID`
        ORDER BY `Movie`.`Title`
    ";
    $records = DB::query($query);

您现在可以使用foreach遍历$records:

    foreach($records as $record) {
        print_r($record);
    }

另请参阅此论坛主题以获取更多信息: http://www.silverstripe.org/general-questions/show/12745

also see this forum thread for additional information:http://www.silverstripe.org/general-questions/show/12745

这篇关于[Silverstripe 2.4]:如何从一对多关系中获取包含列的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！