Foreign key in super class table always NULL (concrete inheritance)

[djairhogeuens]

Hi,

I have the following schema with concrete inheritance defined:

<table name="form">
<column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
<column name="name" type="varchar" size="255" required="true" />
<column name="description" type="varchar" size="500" />
<column name="type" type="enum" valueSet="joining" required="true" />
<column name="start_time" type="timestamp" required="true" />
<column name="end_time" type="timestamp" required="true" />
<column name="max_answers" type="integer" required="true" />
</table>
<table name="question">
<column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
<column name="label" type="varchar" size="255" required="true" />
<column name="type" type="enum" valueSet="text,choice,upload" required="true" />
<column name="form_id" type="integer" />
<foreign-key foreignTable="form">
<reference local="form_id" foreign="id"/>
</foreign-key>
</table>
<table name="text_question">
<column name="max_length" type="integer" />
<column name="multi_line" type="boolean" required="true" />
<behavior name="concrete_inheritance">
<parameter name="extends" value="question" />
</behavior>
</table>
<table name="upload_question">
<column name="file_type" type="varchar" size="255" />
<behavior name="concrete_inheritance">
<parameter name="extends" value="question" />
</behavior>
</table>
<table name="choice_question">
<column name="multi_option" type="boolean" required="true" />
<behavior name="concrete_inheritance">
<parameter name="extends" value="question" />
</behavior>
</table>
<table name="option">
<column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
<column name="name" type="varchar" size="255" required="true" />
<column name="value" type="varchar" size="255" required="true" />
<column name="question_id" type="integer" required="true" />
<foreign-key foreignTable="choice_question">
<reference local="question_id" foreign="id" />
</foreign-key>
</table>

This results in the following database:

CREATE TABLE `form`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` VARCHAR(500),
    `type` TINYINT NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME NOT NULL,
    `max_answers` INTEGER NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM;

-- ---------------------------------------------------------------------
-- question
-- ---------------------------------------------------------------------

CREATE TABLE `question`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `label` VARCHAR(255) NOT NULL,
    `type` TINYINT NOT NULL,
    `form_id` INTEGER,
    `descendant_class` VARCHAR(100),
    PRIMARY KEY (`id`),
    INDEX `question_FI_1` (`form_id`)
) ENGINE=MyISAM;

-- ---------------------------------------------------------------------
-- text_question
-- ---------------------------------------------------------------------

CREATE TABLE `text_question`
(
    `max_length` INTEGER,
    `multi_line` TINYINT(1) NOT NULL,
    `id` INTEGER NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `type` TINYINT NOT NULL,
    `form_id` INTEGER,
    PRIMARY KEY (`id`),
    INDEX `text_question_I_1` (`form_id`)
) ENGINE=MyISAM;

-- ---------------------------------------------------------------------
-- upload_question
-- ---------------------------------------------------------------------

CREATE TABLE `upload_question`
(
    `file_type` VARCHAR(255),
    `id` INTEGER NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `type` TINYINT NOT NULL,
    `form_id` INTEGER,
    PRIMARY KEY (`id`),
    INDEX `upload_question_I_1` (`form_id`)
) ENGINE=MyISAM;

-- ---------------------------------------------------------------------
-- choice_question
-- ---------------------------------------------------------------------

CREATE TABLE `choice_question`
(
    `multi_option` TINYINT(1) NOT NULL,
    `id` INTEGER NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `type` TINYINT NOT NULL,
    `form_id` INTEGER,
    PRIMARY KEY (`id`),
    INDEX `choice_question_I_1` (`form_id`)
) ENGINE=MyISAM;

-- ---------------------------------------------------------------------
-- option
-- ---------------------------------------------------------------------

CREATE TABLE `option`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `value` VARCHAR(255) NOT NULL,
    `question_id` INTEGER NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `option_FI_1` (`question_id`)
) ENGINE=MyISAM;

When creating a new Form object and multiple linked Question objects, only the form_id columns of the subtype tables (text_question, upload_question and choice_question) are set to the id of the form record. The form_id column of the supertype table (question) is never set and is always NULL, the other values in the supertype table have the correct values.

This issue causes the getQuestions() method not to work on Form objects because the related records are not linked by the question table. The getChoiceQuestions(), getTextQuestions() and getUploadQuestions() methods are working just fine.

Can anyone tell me whether this is actually a bug or not? This behaviour is also present in Propel 1.6.

Thanks in advance.

Grts Djairho

[dereuromark]

Is this still relevant or can it be closed?

[djairhogeuens]

I still have the following snippet in my codebase because I never received an answer so yes, it is still relevant:

// overrides Propel implementation due to bug in framework
    public function setQuestions(\Propel\Runtime\Collection\Collection $questions, \Propel\Runtime\Connection\ConnectionInterface $con = null) {
        //parent::setQuestions($questions, $con);
        $this->setTextQuestions(new ObjectCollection(array_filter($questions->getData(), function ($question) {
            return $question instanceof TextQuestion;
        })), $con);
        $this->setChoiceQuestions(new ObjectCollection(array_filter($questions->getData(), function ($question) {
            return $question instanceof ChoiceQuestion;
        })), $con);
        $this->setUploadQuestions(new ObjectCollection(array_filter($questions->getData(), function ($question) {
            return $question instanceof UploadQuestion;
        })), $con);
        $this->setDateQuestions(new ObjectCollection(array_filter($questions->getData(), function ($question) {
            return $question instanceof DateQuestion;
        })), $con);
        $this->setTextSeparators(new ObjectCollection(array_filter($questions->getData(), function ($question) {
            return $question instanceof TextSeparator;
        })), $con);
        return $this;
    }

For your convenience, I also quickly retested it with the version I am currently using (commit 5eb354ef50b65f1f9c58c2f9f1c2523a4378edd7) and the issue is still present there. Did not check it with the latest master version yet though.

[dereuromark]

Can you make a PR here against the current master? That would help to get things fast-tracked.

[djairhogeuens]

@dereuromark I am sorry but I am not acquainted with the Propel internal codebase and also won't have the time in the near future to get any deeper into this...