php - CSV 导出，包括其他表中的行

Question

我正在尝试在 CakePHP 中导出到 CSV。

基本上我遇到的问题是，数据库被分成多个表。一种用于产品，一种用于艺术家，一种用于用户。

除了艺术家的电子邮件地址存储在用户表中之外，一切都运行良好，我希望将这些包含在导出中。我尝试为列(csv)添加适当的标题，然后添加 $结果['用户']['用户名'],可以在下面的完整功能中看到该行，但它将电子邮件字段留空。请帮忙!

function export($event_id)
{
    $this->layout = 'blank_layout';

    ini_set('max_execution_time', 600); //increase max_execution_time to 10 min if data set is very large

    //create a file
    $filename = "export_".date("Y.m.d").".csv";
    $csv_file = fopen('php://output', 'w');

    header('Content-type: application/csv');
    header('Content-Disposition: attachment; filename="'.$filename.'"');

    $results = $this->Sculpture->find('all', array('conditions' => array('event_id' =>       $event_id), 'order' => 'artist_id'));

    // The column headings of your .csv file
    $header_row = array(
        "Artist",
        "Email",
        "Sculpture", 
        "Materials", 
        "Description",
        "Price",
        "Notes"
        );
    fputcsv($csv_file,$header_row,',','"');

// Each iteration of this while loop will be a row in your .csv file where each field corresponds to the heading of the column
    foreach($results as $result)
    {
    // Array indexes correspond to the field names in your db table(s)
        $row = array(
        $result['Artist']['name'],
        $result['Users']['username'],
        $result['Sculpture']['title'],
        $result['Sculpture']['materials'],
        $result['Sculpture']['description'],
        $result['Sculpture']['price'],
        $result['Sculpture']['notes'],
    );

    fputcsv($csv_file,$row,',','"');
    }

    fclose($csv_file);
}

雕塑模型

    <?php
App::uses('AppModel', 'Model');
/**
 * Sculpture Model
 *
 * @property Artist $Artist
 * @property Event $Event
 */
class Sculpture extends AppModel {

/**
 * Validation rules
 *
 * @var array
 */
    public $validate = array(
    'artist_id' => array(
        'numeric' => array(
            'rule' => array('numeric'),
            //'message' => 'Your custom message here',
            //'allowEmpty' => false,
            //'required' => false,
            //'last' => false, // Stop validation after this rule
            //'on' => 'create', // Limit validation to 'create' or 'update' operations
        ),
    ),
    'event_id' => array(
        'numeric' => array(
            'rule' => array('numeric'),
            //'message' => 'Your custom message here',
            //'allowEmpty' => false,
            //'required' => false,
            //'last' => false, // Stop validation after this rule
            //'on' => 'create', // Limit validation to 'create' or 'update' operations
        ),
    ),
    'title' => array(
        'notempty' => array(
            'rule' => array('notempty'),
            'message' => 'Please tell us the name of your sculpture',
            'allowEmpty' => true,
            'required' => false,
            //'last' => false, // Stop validation after this rule
            //'on' => 'create', // Limit validation to 'create' or 'update' operations
        ),
    ),
    'materials' => array(
        'notempty' => array(
            'rule' => array('notempty'),
            'message' => 'Please tell us the materials used in this sculpture',
            'allowEmpty' => true,
            'required' => false,
            //'last' => false, // Stop validation after this rule
            //'on' => 'create', // Limit validation to 'create' or 'update' operations
        ),
    ),
    'price' => array(
        'money' => array(
             'rule'    => array('numeric'),
            'message' => 'Please include the price of your sculpture.'
            //'message' => 'Your custom message here',
            //'allowEmpty' => false,
            //'required' => false,
            //'last' => false, // Stop validation after this rule
            //'on' => 'create', // Limit validation to 'create' or 'update' operations
        ),
    )
);

public function beforeValidate()
{
    //$p = $this->data['Sculpture']['price'];

    // Get decimal place if available
    //$dec = substr(, $start)

    $this->data['Sculpture']['price'] = preg_replace("/[^0-9.]/", '', $this->data['Sculpture']['price']);
    return true;
}
//The Associations below have been created with all possible keys, those that are not needed can be removed

/**
 * belongsTo associations
 *
 * @var array
 */
public $belongsTo = array(
    'Artist' => array(
        'className' => 'Artist',
        'foreignKey' => 'artist_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
    'Event' => array(
        'className' => 'Event',
        'foreignKey' => 'event_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
);


/**
 * hasAndBelongsToMany associations
 *
 * @var array
 */
public $hasAndBelongsToMany = array(
    'MediaFile' => array(
            'className' => 'MediaFile',
            'joinTable' => 'sculptures_media_files',
            'foreignKey' => 'sculpture_id',
            'associationForeignKey' => 'media_file_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
            'deleteQuery' => '',
            'insertQuery' => ''
    )
);

}

新错误报告

$this->loadModel('Artist');
    $artist = $this->Artist->find('first', array('conditions' => array('Artist.user_id' =>        $this->Auth->user('id'))));
    $this->set('artist_id', $artist['Artist']['id']);
EventsController::view() - APP/Controller/EventsController.php, line 78
ReflectionMethod::invokeArgs() - [internal], line ??
Controller::invokeAction() - CORE/Cake/Controller/Controller.php, line 486
Dispatcher::_invoke() - CORE/Cake/Routing/Dispatcher.php, line 187
Dispatcher::dispatch() - CORE/Cake/Routing/Dispatcher.php, line 162
[main] - APP/webroot/index.php, line 92

雕塑模型出现新错误

$results = $this->Sculpture->find('all', array('conditions' => array('event_id' => $event_id), 'order' => 'artist_id', 'recursive' => 2));

    debug($results); die();

新错误

$this->loadModel('Artist');
$artist = $this->Artist->find('first', array('conditions' => array('Artist.user_id' =>         $this->Auth->user('id'))));
$this->set('artist_id', $artist['Artist']['id']);
EventsController::view() - APP/Controller/EventsController.php, line 78
ReflectionMethod::invokeArgs() - [internal], line ??
Controller::invokeAction() - CORE/Cake/Controller/Controller.php, line 486
Dispatcher::_invoke() - CORE/Cake/Routing/Dispatcher.php, line 187
Dispatcher::dispatch() - CORE/Cake/Routing/Dispatcher.php, line 162
[main] - APP/webroot/index.php, line 92

<div class="cake-debug-output">
<span><strong>/app/Controller/SculpturesController.php</strong> (line <strong>70</strong>)</span>
<pre class="cake-debug">
array(
    (int) 0 =&gt; array(
        &#039;Sculpture&#039; =&gt; array(
            &#039;id&#039; =&gt; &#039;462&#039;,
            &#039;artist_id&#039; =&gt; &#039;1&#039;,
            &#039;event_id&#039; =&gt; &#039;1&#039;,
            &#039;title&#039; =&gt; &#039;&#039;,
            &#039;materials&#039; =&gt; &#039;&#039;,
            &#039;description&#039; =&gt; &#039;&#039;,
            &#039;edition&#039; =&gt; &#039;&#039;,
            &#039;price&#039; =&gt; &#039;0.00&#039;,
            &#039;vat&#039; =&gt; false,
            &#039;media_file_id&#039; =&gt; &#039;0&#039;,
            &#039;delivery&#039; =&gt; &#039;2013-12-16&#039;,
            &#039;notes&#039; =&gt; &#039;&#039;,
            &#039;created&#039; =&gt; &#039;2013-12-16 12:52:14&#039;,
            &#039;modified&#039; =&gt; &#039;2013-12-16 12:52:14&#039;
        ),
        &#039;Artist&#039; =&gt; array(
            &#039;id&#039; =&gt; &#039;1&#039;,
            &#039;name&#039; =&gt; &#039;Amanda Noble&#039;,
            &#039;website&#039; =&gt; &#039;www.thefusedgarden.co.uk&#039;,
            &#039;materials&#039; =&gt; &#039;Glass and stainless steel&#039;,
            &#039;location&#039; =&gt; &#039;Northants&#039;,
            &#039;created&#039; =&gt; &#039;2013-04-30 14:53:25&#039;,
            &#039;modified&#039; =&gt; &#039;2013-07-09 15:21:53&#039;,
            &#039;user_id&#039; =&gt; &#039;11&#039;,
            &#039;User&#039; =&gt; array(
                &#039;password&#039; =&gt; &#039;*****&#039;,
                &#039;id&#039; =&gt; &#039;11&#039;,
                &#039;username&#039; =&gt; &#039;noblept@aol.co.uk&#039;,
                &#039;role_id&#039; =&gt; &#039;4&#039;,
                &#039;created&#039; =&gt; &#039;2013-07-01 15:26:40&#039;,
                &#039;modified&#039; =&gt; &#039;2013-07-01 15:26:40&#039;
            ),
            &#039;Sculpture&#039; =&gt; array(
                (int) 0 =&gt; array(
                    &#039;id&#039; =&gt; &#039;138&#039;,
                    &#039;artist_id&#039; =&gt; &#039;1&#039;,
                    &#039;event_id&#039; =&gt; &#039;2&#039;,
                    &#039;title&#039; =&gt; &#039;Midsummer Blue&#039;,
                    &#039;materials&#039; =&gt; &#039;Glass &amp; Stainless Steel&#039;,
                    &#039;description&#039; =&gt; &#039;One of 3 fused glass panels. sold either as a single panel or as a set of 3&#039;,
                    &#039;edition&#039; =&gt; &#039;&#039;,
                    &#039;price&#039; =&gt; &#039;144.00&#039;,
                    &#039;vat&#039; =&gt; false,
                    &#039;media_file_id&#039; =&gt; &#039;0&#039;,
                    &#039;delivery&#039; =&gt; &#039;2013-07-28&#039;,
                    &#039;notes&#039; =&gt; &#039;When sold as a set of three the price is 10% lower.
We will arrive AM, but will not require assistance to install our sculptures&#039;,
                    &#039;created&#039; =&gt; &#039;2013-07-09 15:06:56&#039;,
                    &#039;modified&#039; =&gt; &#039;2013-07-09 15:21:07&#039;
                ),
                (int) 1 =&gt; array(
                    &#039;id&#039; =&gt; &#039;159&#039;,
                    &#039;artist_id&#039; =&gt; &#039;1&#039;,
                    &#039;event_id&#039; =&gt; &#039;2&#039;,
                    &#039;title&#039; =&gt; &#039;Blue evening&#039;,
                    &#039;materials&#039; =&gt; &#039;Glass and Stainless Seel&#039;,
                    &#039;description&#039; =&gt; &#039;One of 3 fused glass panels sold either as a single panel or as a set of 3   ( the other 2 are Midsummer Blue and Blue Haze )&#039;,
                    &#039;edition&#039; =&gt; &#039;&#039;,
                    &#039;price&#039; =&gt; &#039;128.00&#039;,
                    &#039;vat&#039; =&gt; false,
                    &#039;media_file_id&#039; =&gt; &#039;0&#039;,
                    &#039;delivery&#039; =&gt; &#039;2013-07-29&#039;,
                    &#039;notes&#039; =&gt; &#039;Each panel is unique as no piece of fused glass will ever be identical, but it is possible to create a similar, but not exact replica of any panel.
The images shown are of a similar sets, but the actual exhibits are still in the process of creation and an image is not available.&#039;,
                    &#039;created&#039; =&gt; &#039;2013-07-09 15:30:53&#039;,
                    &#039;modified&#039; =&gt; &#039;2013-07-09 16:31:17&#039;
                ),

Answer 1

如果你遵循蛋糕命名惯例，它应该是

$result['User']['username'],

(型号名称必须是单数，不能是复数)

看到Scuplure模型文件后编辑:

因为用户与艺术家模型相关(而不是直接与雕塑模型相关)，所以您必须在查找调用中设置“递归”参数:

$results = $this->Sculpture->find(
    'all', 
    array(
        'conditions' => array('event_id' => $event_id), 
        'order' => 'artist_id',
        'recursive' => 2
    )
);