The composite foreign key points from RentalUnitAllocation to RentalUnit.
Actually, the id attribute is already defining for a RentalUnit. But the
point is, that I need to copy over the values of the lifetime column of the
rental_unit table into the corresponding column of the
rental_unit_allocation table. Perhaps it becomes more clear if one looks at
the SQL definition:
CREATE TABLE rental_unit (
id integer PRIMARY KEY,
lifetime daterange DEFAULT daterange((now())::date, 'infinity'::date, '[)'
::text) NOT NULL
);
CREATE TABLE rental_unit_allocation (
id integer PRIMARY KEY,
rental_unit_id integer NOT NULL,
rental_unit_lifetime daterange NOT NULL,
period daterange DEFAULT daterange((now())::date, 'infinity'::date, '[)'::
text) NOT NULL,
CONSTRAINT rental_unit_allocation_check CHECK ((period <@
rental_unit_period)),
CONSTRAINT rental_unit_allocation_rental_unit_id_fkey
FOREIGN KEY (rental_unit_id, rental_unit_period)
REFERENCES rental_unit(id, period)
MATCH FULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
);
I should explain the semantic behind. It rental unit has a lifetime. (Once,
it is constructed and on some point of time it is teared down again.) Of
course, a person should only be able to rent within this lifetime. Hence,
there is a CHECK constraint that shall ensure that the rental period is a
true sub-interval of the lifetime of the rental unit. Unfortunately,
PostgreSQL only supports CHECK constraints within the same row. Hence, as a
work-around the rental_unit.lifetime needs to be copied over as
rental_unit_allocation.rental_unit_lifetime. (Of course, this unfortunately
result into redudant information.) Now, to enforce that this redudant data
is always in sync, the foreign key includes the lifetime, too.
Am Montag, 5. Januar 2015 17:34:45 UTC+1 schrieb kontakt:
>
> All the entities you pasted have only one @ORM\Id, which is the composite
> one?
>
> On Mon, Jan 5, 2015 at 4:15 PM, <[email protected] <javascript:>>
> wrote:
>
>> I use the latest Symfony framework with the included Doctrine bundle.
>> Doctrine is at version 2.4.
>>
>> I have a entity with a composite foreign key that fails to persist,
>> because the INSERT-statement only includes half of the foreign key.
>>
>> The scenario is simple: We have persons and rental units (aka apartments,
>> rooms, etc.) that can be rented by persons. There is a many-to-many
>> association between persons and rental units with an additional attribute
>> "period" that defines the period of time that a person rents a rental unit.
>> Hence, this many-to-many relation is broken up into two many-to-one
>> relations and into a linking entity "rental unit allocation" that stores
>> the period.
>>
>> Please note, that the type of period is a special PostgreSQL type
>> ("daterange"). I wrote my own type class to handle this. The latter works
>> fine, no problems here.
>>
>> So here is my simplified code (a left out all setters and getters as well
>> as all unimportant attributes):
>> namespace HEK\HEKdbBundle\Entity;
>>
>> use Doctrine\ORM\Mapping as ORM;
>> use Doctrine\Common\Collections\ArrayCollection;
>> use HEK\HEKdbBundle\Type\DateRange;
>>
>> /**
>> * @ORM\Entity
>> * @ORM\Table( name = "person" )
>> */
>> class Person extends Entity {
>>
>> public function __construct( $firstName, $lastName, \DateTime
>> $birthday = null, Gender $gender = null, Country $country = null ) {
>> $this->id = null;
>> $this->rentalUnitAllocations = new ArrayCollection();
>> }
>>
>> /**
>> * @ORM\Id
>> * @ORM\Column( name = "id", type = "integer" )
>> * @ORM\GeneratedValue( strategy = "SEQUENCE" )
>> * @var int
>> */
>> protected $id = null;
>>
>> /**
>> * @ORM\OneToMany( targetEntity = "RentalUnitAllocation", mappedBy =
>> "person" )
>> * @var Doctrine\Common\Collections\ArrayCollection
>> */
>> protected $rentalUnitAllocations = null;
>> }
>>
>>
>> /**
>> * @ORM\Entity
>> * @ORM\Table( name = "rental_unit" )
>> */
>> class RentalUnit extends Entity {
>>
>> public function __construct( DateRange $lifetime ) {
>> $this->id = null;
>> $this->lifetime = clone( $lifetime );
>> $this->allocations = new ArrayCollection();
>> }
>>
>> /**
>> * @ORM\Id
>> * @ORM\Column( name = "id", type = "integer" )
>> * @ORM\GeneratedValue( strategy = "SEQUENCE" )
>> * @var int
>> */
>> protected $id = null;
>>
>> /**
>> * @ORM\Column( name = "lifetime", type = "daterange", nullable = false
>> );
>> * @var DateRange
>> */
>> protected $lifetime = null;
>>
>> /**
>> * @ORM\OneToMany( targetEntity = "RentalUnitAllocation", mappedBy =
>> "rentalUnit" )
>> * @var ArrayCollection
>> */
>> protected $allocations = null;
>> }
>>
>>
>> /**
>> * @ORM\Entity()
>> * @ORM\Table( name = "rental_unit_allocation" )
>> */
>> class RentalUnitAllocation extends Entity {
>>
>> public function __construct( Person $person, RentalUnit $rentalUnit,
>> DateRange $period ) {
>> $this->id = null;
>> $this->person = $person;
>> $this->rentalUnit = $rentalUnit;
>> $this->period = clone( $period );
>> }
>>
>> /**
>> * @ORM\Id
>> * @ORM\Column( name = "id", type = "integer" )
>> * @ORM\GeneratedValue( strategy = "SEQUENCE" )
>> * @var int
>> */
>> protected $id = null;
>>
>> /**
>> * @ORM\ManyToOne( targetEntity = "Person", inversedBy =
>> "rentalUnitAllocations" )
>> * @ORM\JoinColumn( name = "person_id", referencedColumnName = "id",
>> nullable = false )
>> * @var Person
>> */
>> protected $person = null;
>>
>> /**
>> * @ORM\ManyToOne( targetEntity = "RentalUnit", inversedBy =
>> "allocations" )
>> * @ORM\JoinColumns = {
>> * @ORM\JoinColumn( name = "rental_unit_id", referencedColumnName
>> = "id", nullable = false ),
>> * @ORM\JoinColumn( name = "rental_unit_lifetime",
>> referencedColumnName = "lifetime", nullable = false )
>> * }
>> * @var RentalUnit
>> */
>> protected $rentalUnit = null;
>>
>> /**
>> * @ORM\Column( name = "period", type = "daterange", nullable = false )
>> * @var DateRange
>> */
>> protected $period = null;
>> }
>>
>> The code to persist a new rental unit allocation looks like
>> $em = $this->getDoctrine()->getManager();
>> $person = $em->getReference( 'HEKdbBundle:Person', 1 );
>> $rentalUnit = $em->getRepository('HEKdbBundle:RentalUnit')->find( 42 );
>> $period = new DateRange( '2015-01-01', null );
>> $alloc = new RentalUnitAllocation( $person, $rentalUnit, $period );
>> $em->persist( $alloc );
>> $em->flush();
>>
>> And the error message is
>>
>> NotNullConstraintViolationException: An exception occurred while
>> executing 'INSERT INTO rental_unit_allocation (id, period, person_id,
>> rental_unit_id) VALUES (?, ?, ?, ?)' with params [12, "[ 2015-01-01,
>> infinity )", 1, 42]:
>>
>> SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column
>> "rental_unit_lifetime" violates not-null constraint
>>
>> The error message from PostgreSQL is correct, because the INSERT
>> statement actually lacks the column rental_unit_lifetime column. But why?
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "doctrine-user" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <javascript:>.
>> Visit this group at http://groups.google.com/group/doctrine-user.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
