HiveBrain v1.2.0
Get Started
← Back to all entries
patternphpMinor

Query optimization PHP for string concatination

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
phpqueryoptimizationforconcatinationstring

Problem

I have an entity RegistrationForm which has a OneToMany-relationship with SurveyAnswer.

An answer can either have a collection of FormOptions (when the answer is of type MULTIPLE_CHOICE) or a content represented by a string (type TEXT-FIELD, TEXTAREA etc.).

Below is a function inside the RegistrationForm-entity that tries to obtain a string-based representation of the answer's content.

public function getAnswerByColumnId($columnId) {
    foreach ($this->surveyAnswers as $answer) {
        if ($answer->getFormQuestion()->getId() == $columnId) {
            $formOptions = $answer->getFormOptions();
            if (count($formOptions) > 0) {
                $content = '';
                foreach ($formOptions as $key => $option) {
                    if ($key == 0) {
                        $content .= $option->getName();
                    } else {
                        $content .= ' , ' . $option->getName();
                    }
                }
                return $content;
            } else {
                return $answer->getContent();
            }
        }
    }
    return 'No column match, contact admin';
}


I am creating a table out of the registration forms. Each registration form has a few answers that are of a multiple-choice type.

The result is that the foreach ($formOptions as $key => $option) {...-section is pumping the amount of queries up from 200 to 2500. How can I improve this?

Solution

For now, one solution is to concatenate the options into a string while the answer is persisted. This is part of my FormToArrayTransformer:

switch ($_question->getFormField()->getType()) {
    case FormField::CHOICE:
        $content = '';

        foreach($answer as $_key=>$option) {
            $surveyAnswer->addFormOption($option);
            if ($_key == 0) {
                $content .= $option->getName();
            } else {
                $content .= ', ' . $option->getName();
            }
        }

        $surveyAnswer->setContent($content);
        break;


Note that now both the FormOption entities and their string representation is stored. When viewing you don't have to iterate over the options anymore but simply calling answer.getContent() will do. This saved me 2300 queries in a table for 200 forms.

I am still unsure why this is so.

Code Snippets

switch ($_question->getFormField()->getType()) {
    case FormField::CHOICE:
        $content = '';

        foreach($answer as $_key=>$option) {
            $surveyAnswer->addFormOption($option);
            if ($_key == 0) {
                $content .= $option->getName();
            } else {
                $content .= ', ' . $option->getName();
            }
        }

        $surveyAnswer->setContent($content);
        break;

Context

StackExchange Code Review Q#40254, answer score: 3

Revisions (0)

No revisions yet.