Sorry for giving such a late reply. I was working on other projects. Here
is the SQL
CREATE TABLE room (
id integer NOT NULL,
rev integer DEFAULT 1 NOT NULL,
name character varying NOT NULL,
);
CREATE TABLE person (
id integer NOT NULL,
rev integer DEFAULT 1 NOT NULL,
firstname character varying NOT NULL,
lastname character varying NOT NULL
);
CREATE TABLE room_allocation (
id integer NOT NULL,
rev integer DEFAULT 1 NOT NULL,
person_id integer NOT NULL,
room_id integer NOT NULL,
period daterange DEFAULT daterange((now())::date, 'infinity'::date, '[)'::
text) NOT NULL
);
ALTER TABLE ONLY room_allocation
ADD CONSTRAINT room_allocation_room_id_fkey
FOREIGN KEY (room_id) REFERENCES room(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE ONLY room_allocation
ADD CONSTRAINT room_allocation_person_id_fkey
FOREIGN KEY (person_id) REFERENCES person(id) DEFERRABLE INITIALLY
DEFERRED;
And for the sake of completeness my entities look like that
namespace MyCompany\MyBundle\Entity;
use MyCompany\MyBundle\Type\DateExt;
use MyCompany\MyBundle\Type\DateRange;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
/**
* @ORM\Entity
* @ORM\Table( name = "person" )
*/
class Person extends Entity {
public function __construct( $firstName, $lastName ) {
parent::__construct();
$this->id = 0;
$this->revision = 0;
$this->setFirstName( $firstName );
$this->setLastName( $lastName );
$this->roomAllocations = new ArrayCollection();
}
public function getId() {
return $this->id;
}
public function getRevision() {
return $this->revision;
}
public function getFirstName() {
return $this->firstName;
}
public function setFirstName( $newFirstName ) {
if( $this->firstName == $newFirstName ) return $this;
$oldFirstName = $this->firstName;
$this->firstName = $newFirstName;
$this->onPropertyChanged( 'firstName', $oldFirstName, $newFirstName );
return $this;
}
public function getLastName() {
return $this->lastName;
}
public function setLastName( $newLastName ) {
if( $this->lastName == $newLastName ) return $this;
$oldLastName = $this->lastName;
$this->lastName = $newLastName;
$this->onPropertyChanged( 'lastName', $oldLastName, $newLastName );
return $this;
}
public function getRommAllocations() {
return $this->roomAllocations->toArray();
}
public function addRoomAllocation( RoomAllocation $roomAllocation ) {
if( is_null( $roomAllocation ) ) return $this;
if( $this->roomAllocations->contains( $roomAllocation ) ) return $this;
$this->roomAllocations->add( $roomAllocation );
$roomAllocation->setPerson( $this );
return $this;
}
public function removeRoomAllocation( RoomAllocation $roomAllocation,
Person $newPerson = null ) {
if( is_null( $roomAllocation ) ) return $this;
if( !( $this->roomAllocations->contains( $roomAllocation ) ) ) return
$this;
if( $newPerson == $this ) return $this;
$this->roomAllocations->removeElement( $roomAllocation );
$roomAllocation->setPerson( $newPerson );
return $this;
}
/**
* @ORM\Id
* @ORM\Column( name = "id", type = "integer" )
* @ORM\GeneratedValue( strategy = "SEQUENCE" )
*/
protected $id = 0;
/**
* @ORM\Version
* @ORM\Column( name = "rev", type = "integer" )
*/
protected $revision = 0;
/**
* @ORM\Column( name = "firstname", type = "string", nullable = false )
* @var \string
*/
protected $firstName = null;
/**
* @ORM\Column( name = "lastname", type = "string", nullable = false )
* @var \string
*/
protected $lastName = null;
/**
* @ORM\OneToMany( targetEntity = "RoomAllocation", mappedBy = "person" )
* @var Doctrine\Common\Collections\ArrayCollection
*/
protected $roomAllocations = null;
}
/**
* @ORM\Entity
* @ORM\Table( name = "room" )
*/
class Room extends Entity {
public function __construct( $name ) {
parent::__construct();
$this->id = 0;
$this->revision = 0;
$this->setName( $name );
$this->allocations = new ArrayCollection();
}
public function getId() {
return $this->id;
}
public function getRevision() {
return $this->revision;
}
public function getName() {
return $this->name;
}
public function setDisplayName( $newName ) {
if( $this->name == $newName ) return $this;
$oldName = $this->name;
$this->name = $newName;
$this->onPropertyChanged( 'name', $oldName, $newName );
return $this;
}
public function getAllocations() {
return $this->allocations->toArray();
}
public function addAllocation( RoomAllocation $allocation ) {
if( is_null( $allocation ) ) return $this;
if( $this->allocations->contains( $allocation ) ) return $this;
$this->allocations->add( $allocation );
$allocation->setRoom( $this );
return $this;
}
public function removeAllocation( RoomAllocation $allocation, Room
$newRoom = null ) {
if( is_null( $allocation ) ) return $this;
if( !( $this->allocations->contains( $allocation ) ) ) return $this;
if( $newRoom == $this ) return $this;
$this->allocations->removeElement( $allocation );
$allocation->setRoom( $newRoom );
return $this;
}
/**
* @ORM\Id
* @ORM\Column( name = "id", type = "integer" )
* @ORM\GeneratedValue( strategy = "SEQUENCE" )
*/
protected $id = 0;
/**
* @ORM\Version
* @ORM\Column( name = "rev", type = "integer" )
*/
protected $revision = 0;
/**
* @ORM\Column( name = "name", type = "string", nullable = false )
*/
protected $name = null;
/**
* @ORM\OneToMany( targetEntity = "RoomAllocation", mappedBy = "room" )
* @var ArrayCollection
*/
protected $allocations = null;
}
/**
* @ORM\Entity
* @ORM\Table( name = "room_allocation" )
*/
class RoomAllocation extends Entity {
public function __construct( Person $person, Room $room, DateRange
$period ) {
parent::__construct();
$this->id = 0;
$this->revision = 0;
$this->setPerson( $person );
$this->setRoom( $room );
$this->setPeriod( $period );
}
public function getId() {
return $this->id;
}
public function getRevision() {
return $this->revision;
}
public function getPerson() {
return $this->person;
}
public function setPerson( Person $newPerson = null ) {
if( $this->person == $newPerson ) return $this;
$oldPerson = $this->person;
$this->person = $newPerson;
$this->onPropertyChanged( 'person', $oldPerson, $newPerson );
if( $oldPerson ) $oldPerson->removeRoomAllocation( $this, $newPerson );
$newPerson->addRoomAllocation( $this );
return $this;
}
public function getRoom() {
return $this->room;
}
public function setRoom( Room $newRoom = null ) {
if( $this->room == $newRoom ) return $this;
$oldRoom = $this->room;
$this->room = $newRoom;
$this->onPropertyChanged( 'room', $oldRoom, $newRoom );
if( $oldRoom ) $oldRoom->removeAllocation( $this, $newRoom );
$newRoom->addAllocation( $this );
return $this;
}
public function getPeriod() {
return clone( $this->period );
}
public function setPeriod( DateRange $newPeriod ) {
$oldPeriod = $this->period;
$this->period = clone( $newPeriod );
$this->onPropertyChanged( 'period', $oldPeriod, $this->period );
return $this;
}
/**
* @ORM\Id
* @ORM\Column( name = "id", type = "integer" )
* @ORM\GeneratedValue( strategy = "SEQUENCE" )
*/
protected $id = 0;
/**
* @ORM\Version
* @ORM\Column( name = "rev", type = "integer" )
*/
protected $revision = 0;
/**
* @ORM\ManyToOne( targetEntity = "Person", inversedBy =
"roomAllocations" )
* @ORM\JoinColumn( name = "person_id", referencedColumnName = "id",
nullable = false )
*/
protected $person = null;
/**
* @ORM\ManyToOne( targetEntity = "Room", inversedBy = "allocations" )
* @ORM\JoinColumn( name = "room_id", referencedColumnName = "id",
nullable = false )
*/
protected $room = null;
/**
* @ORM\Column( name = "period", type = "date_range", nullable = false )
*/
protected $period = null;
}
Matthias
Am Sonntag, 11. Januar 2015 08:15:16 UTC+1 schrieb Parsifal:
>
> Can't you just convert this sql into DQL?
> Please give table structure for these three tables.
>
--
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.
