i've trouble with a OneToMany relation. I'm currently working on a log analyser, that stores the original log rows as well as the parsed message from the row including the keywords. The Database structure looks like that:
Database
CREATE TABLE IF NOT EXISTS `keywords` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`row_id` bigint(20) unsigned NOT NULL,
`type` varchar(50) NOT NULL,
`value` varchar(128) NOT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `value` (`value`),
KEY `FK_keywords_rows` (`row_id`),
CONSTRAINT `FK_keywords_rows` FOREIGN KEY (`row_id`) REFERENCES `rows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `messages` (
`row_id` bigint(20) unsigned NOT NULL,
`profile_name` varchar(50) NOT NULL,
`type` varchar(50) NOT NULL,
`subtype` varchar(50) NOT NULL,
`time` datetime NOT NULL,
KEY `FK_messages_rows` (`row_id`),
KEY `FK_messages_profiles` (`profile_name`),
CONSTRAINT `FK_messages_profiles` FOREIGN KEY (`profile_name`) REFERENCES `profiles` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_messages_rows` FOREIGN KEY (`row_id`) REFERENCES `rows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `profiles` (
`name` varchar(50) NOT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `rows` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`profile_name` varchar(50) NOT NULL,
`content` varchar(1024) NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_rows_profiles` (`profile_name`),
CONSTRAINT `FK_rows_profiles` FOREIGN KEY (`profile_name`) REFERENCES `profiles` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The log analyser can remove not correctly parsed messages an keywords while the row stays for a reanalisation. That's the reason why messages hasn't an own primary key and keywords reference to the row_id of messages. My Entities looks like that:
Profile
/**
* Class Profile
* @package Ghopple\Entity
* @ORM\Entity
* @ORM\Table(name="profiles")
*/
class Profile
{
/**
* @ORM\Id
* @ORM\Column(name="name", type="string", length=50)
* @var string
*/
protected $name;
/**
* @ORM\Column(name="description", type="string", length=255)
* @var string
*/
protected $description;
/**
* @ORM\OneToMany(targetEntity="Row", mappedBy="profile", fetch="LAZY")
* @var Row[]
*/
protected $rows;
/**
* @ORM\OneToMany(targetEntity="Message", mappedBy="profile", fetch="LAZY")
* @var Message[]
*/
protected $messages;
Row
/**
* Class Row
* @package Ghopple\Entity
* @ORM\Entity
* @ORM\Table(name="rows")
*/
class Row
{
/**
*
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(name="id", type="integer")
* @var integer
*/
protected $id;
/**
* @ORM\ManyToOne(targetEntity="Profile", inversedBy="rows")
* @ORM\JoinColumn(name="profile_name", referencedColumnName="name")
* @var Profile
*/
protected $profile;
/**
* @ORM\Column(name="content", type="string", length=1024)
* @var string
*/
protected $content;
/**
* @ORM\OneToOne(targetEntity="Message", mappedBy="row")
* @var Message
*/
protected $message;
Message
/**
* Class Message
* @package Ghopple\Entity
* @ORM\Entity
* @ORM\Table(name="messages")
*/
class Message
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(name="id", type="integer")
* @var integer
*/
protected $id;
/**
* @ORM\Column(name="type", type="string", length=50)
* @var string
*/
protected $type;
/**
* @ORM\Column(name="subtype", type="string", length=50)
* @var string
*/
protected $subtype;
/**
* @ORM\ManyToOne(targetEntity="Profile", fetch="EAGER")
* @ORM\JoinColumn(name="profile_name", referencedColumnName="name")
* @var Profile
*/
protected $profile;
/**
* @ORM\OneToOne(targetEntity="Row", fetch="LAZY", cascade={"persist"})
* @ORM\JoinColumn(name="row_id", referencedColumnName="id")
* @var Row
*/
protected $row;
/**
* @ORM\OneToMany(targetEntity="Keyword", mappedBy="message", cascade={"persist"})
* @var Keyword[]
*/
protected $keywords;
/**
* @ORM\Column(name="time", type="datetime")
* @var DateTime
*/
protected $time;
Keyword
/**
* Class Keyword
* @package Ghopple\Entity
* @ORM\Entity
* @ORM\Table(name="keywords")
*/
class Keyword
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(name="id", type="integer")
* @var integer
*/
protected $id;
/**
* @ORM\Column(name="type", type="string", length=50)
* @var string
*/
protected $type;
/**
* @ORM\ManyToOne(targetEntity="Message", inversedBy="keywords")
* @ORM\JoinColumn(name="row_id", referencedColumnName="row_id")
* @var Message
*/
protected $message;
/**
* @ORM\Column(name="value", type="string", length=128)
* @var string
*/
protected $value;
And that's what i try to do:
....
$profile = $profileRepository->findOneBy(array('name' => 'ghostpp-01'), array('name' => 'DESC'));
$row = new Row();
$row->setContent('content');
$row->setProfile($profile);
$message = new Message();
$message->setType('type');
$message->setSubtype('subtype');
$message->setTime(new DateTime());
$message->setProfile($profile);
$message->setRow($row);
$keyword = new Keyword();
$keyword->setType('keyword');
$keyword->setValue('value');
$message->addKeyword($keyword);
$entityManager->persist($message);
$entityManager->flush();
....
I've get an exception message in the keyword insert statement:
An exception occurred while executing 'INSERT INTO keywords (type, value, row_id) VALUES (?, ?, ?)' with params ["keyword", "value", null]:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'row_id' cannot be null
I read a lot of entries here and through the internet, thats why i add the following lines on adding keywords to a message:
/**
* @param Keyword $keyword
* @return $this
*/
public function addKeyword(Keyword $keyword)
{
if (!$this->keywords->contains($keyword)) {
$this->keywords->add($keyword);
$keyword->setMessage($this);
}
return $this;
}
/**
* @param Keyword $keyword
* @return $this
*/
public function removeKeyword(Keyword $keyword)
{
if ($this->keywords->contains($keyword)) {
$this->keywords->remove($keyword);
$keyword->unsetMessage();
}
return $this;
}
I'm taking this from http://www.doctrine-project.org/jira/browse/DDC-2244 and it describes the same problem to solve. I set the association to message and to keyword, what should solve the error, but it didn't. Now im out of ideas....
EDIT:
As you can see in the Log, he creates the rows in the correct order, but he can't solve the row_id after insert:
SELECT t0.name AS name1, t0.description AS description2 FROM profiles t0 WHERE t0.name = ? ORDER BY t0.name DESC LIMIT 1
Params 0 => string(10) "ghostpp-01"
Types 0 => string(6) "string"
Time0.011378049850464
"START TRANSACTION"
Params
Types
Time0.00012993812561035
INSERT INTO rows (content, profile_name) VALUES (?, ?)
Params 1 => string(7) "content"
2 => string(10) "ghostpp-01"
Types 1 => string(6) "string"
2 => string(6) "string"
Time0.012298107147217
INSERT INTO messages (type, subtype, time, profile_name, row_id) VALUES (?, ?, ?, ?, ?)
Params 1 => string(4) "type"
2 => string(7) "subtype"
3 => object(stdClass)#511 (3) { ["__CLASS__"]=> string(8) "DateTime" ["date"]=> string(25) "2013-10-09T06:36:29+02:00" ["timezone"]=> string(12) "Europe/Paris" }
4 => string(10) "ghostpp-01"
5 => int(30)
Types 1 => string(6) "string"
2 => string(6) "string"
3 => string(8) "datetime"
4 => string(6) "string"
5 => string(7) "integer"
Time0.00024700164794922
INSERT INTO keywords (type, value, row_id) VALUES (?, ?, ?)
Params 1 => string(7) "keyword"
2 => string(5) "value"
3 => NULL
Types 1 => string(6) "string"
2 => string(6) "string"
3 => NULL
Time0
SQL "ROLLBACK"
