Specific data transformations before SerializedView

Need Help
1

I have two database tables, Foo and Bar. Both have controllers and models. I want to create an API to export spreadsheets from either table. To this end, I have set up a CsvView and a XlsxView. However, the spreadsheet columns needed for Foo are different from Bar (and they are not just the database columns either). To make matters worse, I have some helper methods to serialize e.g. lists in cells.

I thought of converting Foo and Bar entities to table rows in the view (because it is part of creating a representation of the data), but I do not want to create FooCsvView, FooXlsxView, BarCsvView and BarXlsxView because that gets messy quick. I have not found another nice way to do this in the view part. I could perhaps try something with modifying the respective viewVars in a template file and then feed those to the serializer, but that does not seem right.

Instead, I added a getTableRow() method to Entity/Foo.php and Entity/Bar.php, and made a TableExportTrait for the helper methods. Is that a better way of doing things? Alternatively, I could make a helper or view trait with getFooRow(), getBarRow() etc. and use a switch statement, but that sounds a bit extra in comparison.

2

I think you’re on the right track with your getTableRow() idea.

Often, rather than putting a getTableRow() on the entity, I will make a Helper class with a method that takes the entity as an argument. Then your template can do something like this:

foreach ($data as $entity) {

   echo '<sometag>'; 
   echo $this->Helper->makeRow($entity);
   echo '</sometag>';

}

But since you want this one template to work for many types of data you would have to make the helper class swap-able something like this:


/**
 * Somehow determine the kind of data being passed in so you can 
 * get the proper Helper class. There are many ways to do this.
 */
$this->loadHelper('Helper', ['className' => $concreteHelperClass]); 

/**
 * Now, whether you are looping over an array of arrays, 
 * an array of entities, or an array of something else, the proper 
 * helper is in place and can satisfy your template
 */
foreach ($data as $datum) {

   echo '<sometag>'; 
   echo $this->Helper->makeRow($entity);
   echo '</sometag>';

}

Your Helper interface can be expanded to deliver whatever your template needs.

$this->loadHelper('Helper', ['className' => $concreteHelperClass]); 

foreach ($data as $datum) {

   echo $this->Helper->startRow($entity);

   foreach ($this->Helper->columns($entity) as $column) {
      echo $this->makeCell($column);
   }

   echo $this->Helper->endRow();

}

If you go in this direction I recommend writing an Interface for your Helper classes to keep them all working nicely with your template.

Note
A lot of my psuedo-code makes no sense in real life. It’s just to give you a general idea :slight_smile:

1 Like
3

I tend to do these sorts of things with per-class configuration. So, I’d write a single bit of code (whether an element, helper, etc. depends on the particular usage) which queries the provided data for its static configuration and works from that. So, here I’d make the entity classes include a list of fields to be output in the CSV row, and then the shared code iterates over that and generates output using those field names.

2 Likes
4

I use FriendsOfCake/cakephp-csvview which you pass some fields to the view. I make a special action, which calls a finder, and I pass the columns of the select as the column names. I guess you could adapt XlsxView to make something like that

// src/Model/Table/FooTable.php
public function findCsv(Query $query, array $options)
{
    return $query
        // make joins, group, etc
        ->select([
            'Name' => 'Foo.name',
            'Birth Date' => 'Foo.birth_date',
            // ....
        ]);
}

// src/Controller/FooController.php
use Cake\Datasource\ResultSetInterface;
public function export()
{
    $query = $this->Foo
        ->find('search', ['search' => $this->request->getQuery()])
        // some othere where, etc...
        ->find('csv');
    $_header = array_keys($query->clause('select'));
    $query
        ->disableHydration()
        ->formatResults(function (ResultSetInterface $results) use ($_header) {
            // Unset any virtual/contain properties not in select
            return $results->map(function (array $item) use ($_header) {
                foreach (array_keys($item) as $column) {
                    if (!in_array($column, $_header)) {
                        unset($item[$column]);
                    }
                }

                return $item;
            });
        });

    $_delimiter = ';';
    $_enclosure = '"';
    $_serialize = 'data';
    $_bom = true;

    $this->viewBuilder()->setClassName('CsvView.Csv');
    $this->set('data', $query);
    $this->set(compact(
        'data',
        '_serialize',
        '_delimiter',
        '_enclosure',
        '_header',
        '_bom'
    ));
}

That way you only have to implement findCsv on each Model

1 Like
5

I think I like this one best, it is working well for me so far. Thanks everyone!