My app is running on Symfony framework with Doctrine and MSSQL database. I have a table with users where identifiers are strings (3 characters), for example mni, mdr, owu, etc. In other related tables I'm using these identifiers, but sometimes these identifiers are saved in uppercase letters (for example MNI) or lowercase letters (for example owu).
Example table:
| ID | Name | Description | CreatedBy | UpdatedBy |
|----|-------------|-------------|-----------|-----------|
| 1 | Something 1 | Desc 1 | mni | OWU |
| 2 | Something 2 | Desc 2 | mni | MNI |
| 3 | Something 3 | Desc 3 | MDR | mdr |
My problem is, when I fetch data from table like above, then I don't get complete information about related users. For example, when I fetch record with ID 2, then I have only information about who updated object (I have access to information about this user), but who created object, I get empty user object (with no data). In profiler I noticed that Doctrine do two queries (for user with "mni" and for user with "MNI"), but I get only information about one of them (even though it is the same user).
In my opinion Doctrine treat "mni" and "MNI" as two different objects but this is the same user.
This is entity code with user:
<?php
namespace App\Entity;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;
use Symfony\Component\Validator\Constraints as Assert;
/**
* @ORM\Entity()
* @ORM\Table(name="users")
*
* @UniqueEntity("id")
*/
class User
{
/**
* @var string|null - 3-characters user shortname
*
* @Assert\NotBlank()
* @Assert\Length(max="50")
*
* @ORM\Id()
* @ORM\Column(name="id", type="string", length=50)
*/
private $id;
/**
* @var string|null - Firstname
*
* @Assert\Length(max="50")
*
* @ORM\Column(name="firstname", type="string", length=50, nullable=true)
*/
private $firstName;
/**
* @var string|null - Lastname
*
* @Assert\Length(max="50")
*
* @ORM\Column(name="lastname", type="string", length=50, nullable=true)
*/
private $lastName;
/**
* @return string
*/
public function __toString()
{
return (string) null !== $this->getId() ? ('['.$this->getCode().'] '.$this->getFirstName().' '.$this->getLastName()) : '';
}
/**
* @return string|null
*/
public function getId(): ?string
{
return $this->id;
}
/**
* @param string $id
*
* @return User
*/
public function setId(string $id): User
{
$this->id = $id;
return $this;
}
/**
* @return string|null
*/
public function getFirstName(): ?string
{
return $this->firstName;
}
/**
* @param string|null $firstName
*
* @return User
*/
public function setFirstName(?string $firstName): User
{
$this->firstName = $firstName;
return $this;
}
/**
* @return string|null
*/
public function getLastName(): ?string
{
return $this->lastName;
}
/**
* @param string $lastName
*
* @return User
*/
public function setLastName(?string $lastName): User
{
$this->lastName = $lastName;
return $this;
}
}
This is entity code of another related object (for examaple Project):
<?php
namespace App\Entity;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
/**
* @ORM\Entity()
* @ORM\Table(name="projects")
*/
class Project
{
/**
* @var int|null
*
* @ORM\Id()
* @ORM\GeneratedValue(strategy="IDENTITY")
* @ORM\Column(name="id", type="integer")
*/
private $id;
/**
* @var string|null - Project name
*
* @Assert\NotBlank()
* @Assert\Length(max="50")
*
* @ORM\Column(name="name", type="string")
*/
private $name;
/**
* @var string|null - Project description
*
* @ORM\Column(name="description", type="text")
*/
private $description;
/**
* @var User|null - Who created
*
* @ORM\ManyToOne(targetEntity="App\Entity\User")
* @ORM\JoinColumn(name="created_by", referencedColumnName="id")
*/
private $createdBy;
/**
* @var User|null - Who updated
*
* @ORM\ManyToOne(targetEntity="App\Entity\User")
* @ORM\JoinColumn(name="updated_by", referencedColumnName="id")
*/
private $updatedBy;
/**
* @return string
*/
public function __toString()
{
return (string) $this->getId() ? '#'.$this->getId() : '';
}
/**
* @return int|null
*/
public function getId(): ?int
{
return $this->id;
}
/**
* @return string|null
*/
public function getName(): ?string
{
return $this->name;
}
/**
* @param string $name
*
* @return Project
*/
public function setName(string $name): Project
{
$this->name = $name;
return $this;
}
/**
* @return string|null
*/
public function getDescription(): ?string
{
return $this->description;
}
/**
* @param string|null $description
*
* @return Project
*/
public function setDescription(?string $description): Project
{
$this->description = $description;
return $this;
}
/**
* @return User|null
*/
public function getCreatedBy(): ?User
{
return $this->createdBy;
}
/**
* @param User|null $createdBy
*
* @return Project
*/
public function setCreatedBy(?User $createdBy): Project
{
$this->createdBy = $createdBy;
return $this;
}
/**
* @return User|null
*/
public function getUpdatedBy(): ?User
{
return $this->updatedBy;
}
/**
* @param User|null $updatedBy
*
* @return Project
*/
public function setUpdatedBy(?User $updatedBy): Project
{
$this->updatedBy = $updatedBy;
return $this;
}
}
When I fetch Project entity and dump this object I gets this:
Project {#336 ▼
-id: 2
-name: "Something 2"
-description: "Desc 2"
-createdBy: User {#356 ▼
+__isInitialized__: false
-code: "MNI"
-firstName: null
-lastName: null
…2
}
-updatedBy: User {#355 ▼
+__isInitialized__: true
-code: "mni"
-firstName: "John"
-lastName: "Doe"
…2
}
}
Please notice that User object from "createdBy" have different object ID (#356) than User object from "updatedBy" (#355).
When I want to fetch for example User firstname who created Project, then I get null, but when I want to fetch user firstname who updated Project, then I get "John".
Has anyone encountered a similar situation? Thanks for your help.