0

I have a problem, I have to export a csv with platform api and we use data transformer and dto for that. I have a collection in my entity and I would like to export this collection in a single row grouped by user id. I can explain in sql what I would like to output in this export:

SELECT 
name,
fistname,
MAX(CASE WHEN type = 1 THEN date END) type1, 
MAX(CASE WHEN type = 2 THEN date END) type2, 
MAX(CASE WHEN type = 3 THEN date END) type3, 
MAX(CASE WHEN type = 4 THEN date END) type4, 
MAX(CASE WHEN type = 5 THEN date END) type5,
MAX(CASE WHEN type = 6 THEN date END) type6, 
MAX(CASE WHEN type = 7 THEN date END) type7, 
MAX(CASE WHEN type = 8 THEN date END) type8
FROM documents
GROUP BY user_id

For information the frontend is on vuejs, on Symfony we don't use controllers (at least not for csv exports). I only see the entity or the data transformer to filter my data.

I tried with a matching and Criteria, like :

$criteria = Criteria::create();
$expression = $criteria::expr()->eq("type", 1);
$query = $criteria->andWhere($expression);
$documents = $this->getDocuments()->matching($query);

The criteria work for a count but if i want the values in the problem is that Criteria just returns me the string url of ArrayCollection: /Doctrine/Common/ArrayCollection (i don't remember exactly the string). I would just like to output a date field from this collection. I feel like Criteria can't output values and it's just validation right? How can I, from my entity, have the same result on several columns in my export like column type1 is like $document->type1 ?

I also tried another solution:

public function getDate(int $type): string
    {
        return ($this->type = $type) ? $this->date : '0000-00-00';
    }

in my collection then:

public function getDateType1(): string
    {
        $document = "";
        foreach ($this->documents as $value) {
            $document = $value->getDate(1);
        }
        return $document;
    }

in my entity but the output date is the same. I also tried to put my export in the collection directly but behind I find myself in the csv with several lines per user (the dates on the other hand are good but not on a single line), which is normal since it does not there is no groupBy(). thank you.

Edit : I try an array_map :

$userCsv->document = array_map(function($document) {
return [
"type" => $this->getType($document->getType()), //return the type(1 to 8)
"date" => ($document->getStatus() == 1) ? $document->getDate() : ''
];
}, $object->getDocuments()->toArray());

then it works a bit except that I can't customize the fields of the columns and I can't organize the data by ascending type either. I would have preferred to have a column per type and put the date in this column for example ["type1" => 2022-11-12] like the when case would have done.

0 Answers0