Export Riddle content

You can use the Riddle API to retrieve details of a specific Riddle Riddle API and export the content of the Riddle as CSV. This is a basic example of how you can retrieve the details of a Riddle using the Riddle API in a Symfony PHP controller and export the content of the Riddle as CSV.

Step 1

Create an API Access Token here: Access Token. Important: Delete this token after you finish testing here.

Example Symfony PHP controller

This is an example of how you can retrieve the details of a Riddle and export to csv using the Riddle API in a Symfony PHP controller.

<?php

// src/Controller/RiddleApiController.php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Attribute\Route;
use Symfony\Contracts\HttpClient\HttpClientInterface;
use Symfony\Component\Serializer\SerializerInterface;
use Symfony\Component\HttpFoundation\JsonResponse;

class ResponseDTO
{
    public string $message;
    public int $status;
    public $data;
}

class RiddleApiController extends AbstractController
{
    #[Route('/api/riddle/csv/{uuid}', name: 'app_riddle_csv', methods: ['POST'])]
    public function getRiddleCsv(string $uuid, Request $request, HttpClientInterface $httpClient, SerializerInterface $serializer): JsonResponse
    {
        // read the access token from the request json body post
        $data = json_decode($request->getContent(), true);
        $riddleAccessToken = $data['riddleAccessToken'] ?? null;

        if ($riddleAccessToken === null) {
            return new JsonResponse(['message' => 'Missing riddleAccessToken'], 400);
        }

        $client = $httpClient->withOptions([
            'base_uri' => 'https://www.riddle.com',
            'headers' => [
                'X-RIDDLE-BEARER' => 'Bearer ' . $riddleAccessToken,
            ]
        ]);

        $response = $client->request(
            'GET',
            '/api/v3/riddle/' . $uuid
        );

        $statusCode = $response->getStatusCode();

        $responseDTO = new ResponseDTO();
        $responseDTO->status = $statusCode;

        if ($statusCode !== 200) {
            $responseDTO->message = 'Error';

            return new JsonResponse($responseDTO, $statusCode);
        }

        $content = $response->toArray();

        $responseDTO->message = 'Success';

        $riddleData = $content['data'];
        $riddleDataAsJsonString = json_encode($riddleData);

        // CSV-Inhalt generieren
        $csvContent = $this->csvGenerator->generateCsvFromJson($riddleDataAsJsonString);

        $responseDTO->data = $csvContent;

        return new JsonResponse($responseDTO, $statusCode);
    }
}

Required PHP library

Installation der League CSV-Bibliothek:

composer require league/csv

CSV Generator Service

This is an example of a service that generates a CSV from the JSON data of a Riddle.

<?php

// srv/Service/CsvGeneratorService.php

namespace App\Service;

use League\Csv\Writer;
use SplTempFileObject;

class CsvGeneratorService
{
    /**
     * Creates a CSV from the JSON data.
     *
     * @param string $jsonContent JSON content as a string
     * @return string Content of the generated CSV
     */
    public function generateCsvFromJson(string $jsonContent): string
    {
        // Decode JSON content
        $data = json_decode($jsonContent, true);

        // Create CSV header
        $headers = ['Index', 'Type', 'TypeGroup', 'Title', 'Description', 'Media'];

        // Determine the maximum number of answers per question
        $maxAnswers = $this->getMaxAnswers($data['data']['blocks']);

        for ($i = 1; $i <= $maxAnswers; $i++) {
            $headers[] = "Answer {$i} Title";
            $headers[] = "Answer {$i} Description";
            $headers[] = "Answer {$i} Media";

            if ($data['type'] === 'Quiz') {
                $block = $data['data']['blocks'][$i - 1] ?? [];

                if ($this->isRightOrWrong($block)) {
                    $headers[] = "Answer {$i} IsCorrect";
                }
            }
        }

        if ($data['type'] === 'Personality') {
            $index = 1;

            foreach ($data['data']['personalities'] as $personalityItem) {
                $headers[] = "Personality {$index} Title";

                $headers[] = "Personality {$index} Winning Result Title";
                $headers[] = "Personality {$index} Winning Result Description";
                $headers[] = "Personality {$index} Winning Result Media";

                $index++;
            }

            $index = 1;

            foreach ($data['data']['attributes'] as $attributeItem) {
                $headers[] = "Attribute {$index} Title";

                $headers[] = "Attribute {$index} Winning Result Title";
                $headers[] = "Attribute {$index} Winning Result Description";
                $headers[] = "Attribute {$index} Winning Result Media";

                $index++;
            }
        }

        // Initialise CSV Writer
        $csv = Writer::createFromFileObject(new SplTempFileObject());
        $csv->insertOne($headers);

        // Initial index
        $index = 1;

        // Run through blocks (questions)
        if (isset($data['data']['blocks'])) {
            foreach ($data['data']['blocks'] as $block) {
                $type = $block['type'] ?? '';
                $typeGroup = $block['typeGroup'] ?? '';
                $content = $block['content'] ?? [];
                $title = $this->formatStringForCsv(strip_tags($content['title'] ?? ''));
                $description = $this->formatStringForCsv(strip_tags($content['description'] ?? ''));

                // Format the media URL of the question
                $media = $this->formatStringForCsv($content['media']['source']['url'] ?? '');

                // Prepare answers
                $answers = [];
                if (isset($content['pollItems'])) {
                    $answers = $this->extractAnswers($block);

                    if (isset($content['pollOtherItems']) && $this->isOtherTextEntryVisible($block)) {
                        $answers = array_merge($answers, $this->extractAnswers($block));
                    }
                }

                if (isset($content['pollTextEntry'])) {
                    $answers = array_merge($answers, $this->extractAnswers($block));
                }

                if (isset($content['swiperItems'])) {
                    $answers = array_merge($answers, $this->extractAnswers($block));
                }

                if (isset($content['matrixConfig'])) {
                    $answers = $this->extractAnswers($block);
                }

                if (isset($content['quizItems'])) {
                    $answers = $this->extractAnswers($block);
                }

                if (isset($content['personalityItems'])) {
                    $answers = $this->extractAnswers($block);
                }

                if (isset($content['predictorItems'])) {
                    $answers = $this->extractAnswers($block);
                }

                // Fill in missing answers
                for ($i = count($answers); $i < $maxAnswers; $i++) {
                    $answers[] = ['title' => '', 'description' => '', 'media' => ''];
                }

                // Zeile erstellen
                $row = [
                    $index++,
                    $type,
                    $typeGroup,
                    $title,
                    $description,
                    $media
                ];

                // Add answers
                foreach ($answers as $answer) {
                    $row[] = $this->formatStringForCsv($answer['title']);
                    $row[] = $this->formatStringForCsv($answer['description']);
                    $row[] = $this->formatStringForCsv($answer['media']);

                    if ($block['typeGroup'] === 'Quiz' && $this->isRightOrWrong($block)) {
                        $row[] = $answer['isCorrect'] ?? '';
                    }
                }

                // Add personalities and attributes
                if ($data['type'] === 'Personality') {
                    $personalities = $this->extractPersonalitiesAndAttributes($data['data'], 'personalities');

                    foreach ($personalities as $personality) {
                        $row[] = $this->formatStringForCsv($personality['title']);
                        $row[] = $this->formatStringForCsv($personality['winningResultTitle']);
                        $row[] = $this->formatStringForCsv($personality['winningResultDescription']);
                        $row[] = $this->formatStringForCsv($personality['winningResultMedia']);
                    }

                    $attributes = $this->extractPersonalitiesAndAttributes($data['data'], 'attributes');

                    foreach ($attributes as $attribute) {
                        $row[] = $this->formatStringForCsv($attribute['title']);
                        $row[] = $this->formatStringForCsv($attribute['winningResultTitle']);
                        $row[] = $this->formatStringForCsv($attribute['winningResultDescription']);
                        $row[] = $this->formatStringForCsv($attribute['winningResultMedia']);
                    }
                }

                // Write line to CSV
                $csv->insertOne($row);
            }
        }

        // Return CSV content
        return $csv->toString();
    }

    /**
     * Determines the maximum number of answers per question.
     */
    private function getMaxAnswers(array $blocks): int
    {
        $maxAnswers = 0;

        foreach ($blocks as $block) {
            if (isset($block['content']['pollItems'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['pollItems']));
            }

            if (isset($block['content']['pollOtherItems']) && $this->isOtherTextEntryVisible($block)) {
                $maxAnswers++;
            }

            if (isset($block['content']['matrixConfig'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['matrixConfig']['items']));
            }

            if (isset($block['content']['quizItems'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['quizItems']));
            }

            if (isset($block['content']['personalityItems'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['personalityItems']));
            }

            if (isset($block['content']['predictorItems'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['predictorItems']));
            }

            if (isset($block['content']['swiperItems'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['swiperItems']));
            }

            if (isset($block['content']['pollTextEntry'])) {
                $maxAnswers++;
            }

            if (isset($block['content']['predictorItems'])) {
                $maxAnswers = max($maxAnswers, count($block['content']['predictorItems']));
            }
        }

        return $maxAnswers;
    }

    /**
     * Check if isOtherTextEntryVisible is set and return the value.
     */
    private function isOtherTextEntryVisible(array $block): bool
    {
        return $block['options']['isOtherTextEntryVisible'] ?? false;
    }

    /**
     * Check if the block is a right or wrong quiz.
     */
    private function isRightOrWrong(array $block): bool
    {
        return isset($block['options']['quizAnswerOptions']) && ($block['options']['quizAnswerOptions']['isRightOrWrong'] ?? false);
    }

    /**
     * Formats a string to avoid CSV problems.
     */
    private function formatStringForCsv(?string $input): string
    {
        return $input ? "\"$input\"" : '';
    }

    /**
     * Extracts answers from a block.
     */
    private function extractAnswers(array $block): array
    {
        $items = [];

        if (isset($block['content']['pollItems'])) {
            $items = $block['content']['pollItems'];
        } else if (isset($block['content']['swiperItems'])) {
            $items = $block['content']['swiperItems'];
        } else if (isset($block['content']['matrixConfig'])) {
            $items = $block['content']['matrixConfig']['items'];
        } else if (isset($block['content']['quizItems'])) {
            $items = $block['content']['quizItems'];
        } else if (isset($block['content']['personalityItems'])) {
            $items = $block['content']['personalityItems'];
        } else if (isset($block['content']['predictorItems'])) {
            $items = $block['content']['predictorItems'];
        } else if (isset($block['content']['pollOtherItems'])) {
            $items = $block['content']['pollOtherItems'];
        } else if (isset($block['content']['pollTextEntry'])) {
            $items = [$block['content']['pollTextEntry']];
        }

        $answers = [];
        foreach ($items as $item) {
            $title = strip_tags($item['title'] ?? '');
            $description = strip_tags($item['description'] ?? '');
            $media = $this->formatStringForCsv($item['media']['source']['url'] ?? '');

            $answers[] = [
                'title' => $title,
                'description' => $description,
                'media' => $media
            ];

            if ($block['typeGroup'] === 'Quiz' && $this->isRightOrWrong($block)) {
                $answers[count($answers) - 1]['isCorrect'] = $item['isCorrect'] ? 'true' : 'false';
            }
        }
        return $answers;
    }

    /**
     * Extracts personalities from a riddle
     */
    private function extractPersonalitiesAndAttributes(array $riddle, string $key): array
    {
        $result = [];

        foreach ($riddle[$key] as $personality) {
            $title = strip_tags($personality['content']['title'] ?? '');
            $winningResult = $personality['content']['winningResult'] ?? [];
            $winningResultTitle = strip_tags($winningResult['title'] ?? '');
            $winningResultDescription = strip_tags($winningResult['description'] ?? '');
            $winningResultMedia = $this->formatStringForCsv($winningResult['media']['source']['url'] ?? '');

            $result[] = [
                'title' => $title,
                'winningResultTitle' => $winningResultTitle,
                'winningResultDescription' => $winningResultDescription,
                'winningResultMedia' => $winningResultMedia
            ];
        }

        return $result;
    }
}