Sorry, there were some copy-and-paste errors. This is the correct SQL:
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_lifetime)),
CONSTRAINT rental_unit_allocation_rental_unit_id_fkey
FOREIGN KEY (rental_unit_id, rental_unit_lifetime)
REFERENCES rental_unit(id, lifetime)
MATCH FULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
);
Am Montag, 5. Januar 2015 18:06:45 UTC+1 schrieb [email protected]:
>
> 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]> 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].
>>> 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.
>>>
>>
>>
--
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.
