CakePHP中的多维数组=> CSV导出

Question

I'm trying to export data from my model and two related models into a CSV file in my CakePHP 2.4 app, and need some help doing it in a computationally non-expensive manner.

In my app, each Post hasMany Field and each Field belongsTo Fieldtype. Fields stores values, and Fieldtype stores the value types (IE "Oxygen concentration"). On each Post, there can be multiple Fields with the same Fieldtype.

The problem with CSV generation here is that each Post's Fields require a different number of columns, so when I'm doing my fputcsv I'm going to need to add empty cells for when any Post has less than the maximum number of fields for, say, Helium so the headers line up.

Is there a SQL solution that will somehow let me do a join on my Posts find and return an array of Fieldtypes with the maximum number of Fields needed for each Fieldtype?

My DB structure:

Posts
id

Fields
id    post_id    fieldtype_id    value

Fieldtypes
id    name

Answer 1

I wasn't able to find an easy way to get the field count in SQL, and as it turns out doing it in PHP isn't too slow because it's not like I'm accessing the database each time.

If anyone else has to deal with this problem, here's what I did:

//Where $posts is my array of Posts and $fieldtypes is an id => name array of my column types
public function makeCSV($posts = array(), $fieldtypes = array()){
    $fields = array();
    $fieldlist = array();
    foreach($posts as $post){
        foreach($post['Field'] as $field){
            if($field['value'] != ''){
                $fields[] = $field['fieldtype_id'];
                $fieldlist[$field['fieldtype_id']] = null;
            }
        } //Get a list of all the fields for that post

        $postcount = array_count_values($fields);
        $fields = array();

        // get a per-post count of fields

        foreach($postcount as $id => $count){
            if ($count > $fieldlist[$id] || !isset($fieldlist[$id])){
                $fieldlist[$id] = $count;
            }
        }
    }

        $output = null;     
        $output .= "Latitude" . ",";
        $output .= "Longitude" . ",";
        $output .= "Sighted (UTC)" . ",";
        $output .= "Posted (UTC)" . ",";
        $output .= "User" . ",";
        $output .= "Location" . ",";
        $output .= "Tags" . ",";
        $output .= "Category" . ",";
        $output .= "Species status" . ",";
//      $output .= "Comments" . ",";

    foreach ($fieldlist as $fieldtype => $count){
        $total = $count;
        while($count != 0){
            $output .= $fieldtypes[$fieldtype] . " " . ($total + 1 - $count)  . ",";
            --$count;
        }
    }
        $output = $output . "
";

    foreach ($posts as $post) {
        $output = $output . addslashes($post['Post']['lat']) . "," . addslashes($post['Post']['lng']) . ",";
        $output = $output . "\"" . addslashes($post['Post']['sighted']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['Post']['created']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['User']['username']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['Post']['location']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['Post']['tags']) . "\"" . ",";
        $output = $output . addslashes($post['Post']['category']) . ",";
        if ($post['Post']['status'] == 1) {
            $output .= "Present,";
        } elseif($post['Post']['status'] == 2) {
            $output .= "Recently Appeared,";
        } elseif ($post['Post']['status'] == 3) {
            $output .= "Disappeared,";
        }
        else {
            $output .= ",";
        }
//      $output = $output . "\"" . addslashes(str_replace(array("
", "\t", ""), '', $post['Post']['body'])) . "\"" . ",";

        foreach ($fieldlist as $fieldtype => $count){
            foreach($post['Field'] as $field){
                if($field['fieldtype_id'] == $fieldtype){
                    $output .= $field['value'] . ",";
                    --$count;
                }
            }
            while ($count > 0){
                $output .= ",";
                --$count;
            }
        }
        $output = $output . "
";   
    }
    return $output; 
}