Hello there have something i dont understand about SQL i guess. I linked below the code and the image about my db shema for help you to understand. So my problem with this request is that it return an empty array and i dont understand why its not working as from my understanding it should return an array containing all the "r.messages" that have the specified ID related to the fields relation. What do i do wrong ?
PS: for some context helping your understanding, im trying to build a messagery system from a user to another. The SQL request i try to do should return the conversation between two users.
public function listOfMessages($value, $value2)
{
return $this->createQueryBuilder('user')
->select('r.message')
->innerJoin('user.sender', 's')
->innerJoin('user.receiver', 'r')
->where('s.sender = :value')
->andWhere('r.recipient = :value2')
->setParameter(':value', $value)
->setParameter(':value2', $value2)
->getQuery()
->getResult()
;
}
Entity User
use App\Repository\PrivateMessageRepository;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass=PrivateMessageRepository::class)
*/
class PrivateMessage
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
private $id;
/**
* @ORM\Column(type="text", nullable=true)
*/
private $message;
/**
* @ORM\ManyToOne(targetEntity=User::class, inversedBy="sender")
*/
private $sender;
/**
* @ORM\ManyToOne(targetEntity=user::class, inversedBy="receiver")
*/
private $recipient;
/**
* @ORM\Column(type="boolean", nullable=true)
*/
private $isRead = 0;
/**
* @ORM\Column(type="datetime_immutable", nullable=true)
*/
private $sentAt;
public function getId(): ?int
{
return $this->id;
}
public function getMessage(): ?string
{
return $this->message;
}
public function setMessage(?string $message): self
{
$this->message = $message;
return $this;
}
public function getSender(): ?User
{
return $this->sender;
}
public function setSender(?User $sender): self
{
$this->sender = $sender;
return $this;
}
public function getRecipient(): ?user
{
return $this->recipient;
}
public function setRecipient(?user $recipient): self
{
$this->recipient = $recipient;
return $this;
}
public function getIsRead(): ?bool
{
return $this->isRead;
}
public function setIsRead(?bool $isRead): self
{
$this->isRead = $isRead;
return $this;
}
public function getSentAt(): ?\DateTimeImmutable
{
return $this->sentAt;
}
public function setSentAt(?\DateTimeImmutable $sentAt): self
{
$this->sentAt = $sentAt;
return $this;
}
}
Entity privateMessage
use App\Repository\PrivateMessageRepository;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass=PrivateMessageRepository::class)
*/
class PrivateMessage
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
private $id;
/**
* @ORM\Column(type="text", nullable=true)
*/
private $message;
/**
* @ORM\ManyToOne(targetEntity=User::class, inversedBy="sender")
*/
private $sender;
/**
* @ORM\ManyToOne(targetEntity=user::class, inversedBy="receiver")
*/
private $recipient;
/**
* @ORM\Column(type="boolean", nullable=true)
*/
private $isRead = 0;
/**
* @ORM\Column(type="datetime_immutable", nullable=true)
*/
private $sentAt;
public function getId(): ?int
{
return $this->id;
}
public function getMessage(): ?string
{
return $this->message;
}
public function setMessage(?string $message): self
{
$this->message = $message;
return $this;
}
public function getSender(): ?User
{
return $this->sender;
}
public function setSender(?User $sender): self
{
$this->sender = $sender;
return $this;
}
public function getRecipient(): ?user
{
return $this->recipient;
}
public function setRecipient(?user $recipient): self
{
$this->recipient = $recipient;
return $this;
}
public function getIsRead(): ?bool
{
return $this->isRead;
}
public function setIsRead(?bool $isRead): self
{
$this->isRead = $isRead;
return $this;
}
public function getSentAt(): ?\DateTimeImmutable
{
return $this->sentAt;
}
public function setSentAt(?\DateTimeImmutable $sentAt): self
{
$this->sentAt = $sentAt;
return $this;
}
}
CodePudding user response：
Could you please use the web debug toolbar to extract the readable query and fire it agains your sql server.
I think your repository dql is wrong - hope i figure it out of the head correct.
- You have to go over the message repository, not over the user repo.
- You have to select the messages for user a and user b visaverce
$qb = $this->createQueryBuilder('m');
$qb->where(
$qb->expr()->orX(
$qb->expr()->andX(
$qb->expr()->eq('m.sender', ':sender'),
$qb->expr()->eq('m.recipient', ':recipient')
),
$qb->expr()->andX(
$qb->expr()->eq('m.sender', ':recipient'),
$qb->expr()->eq('m.recipient', ':sender')
)
)
)
->setParameter(':sender', $sender)
->setParameter(':recipient', $recipient)
->getQuery()
->getResult()
You say you want the conversation between the two users. Architectural i think, there is a missing table named conversation. Think about that:
- A user can have a conversation to one-or-many users
- A conversation can have one-or-many message(s)
- A message can have a sender and a recipient
Maybe a better solution if you want to have more than one conversation ...
CodePudding user response：
Thank you for your time and tips, i tried your dql request and it return "NULL". About doing it into the message repo instead of user repo i did it like this because in my entity user i have two methods that can retrieve messages sent and received. But anyways even trying it inside message repo return an empty array.
I tryed with rawsql and got some good result:
public function stack($id)
{
$rawSql =
"SELECT pm.sent_at as date, pm.message, user.login as login FROM private_message as pm
INNER JOIN user on user.id = pm.sender_id
WHERE pm.sender_id = $id
UNION
SELECT pm.sent_at as date, pm.message, user.login as login FROM private_message as pm
INNER JOIN user on user.id = pm.recipient_id
WHERE pm.recipient_id = $id
ORDER BY date DESC"
;
$conn = $this->getEntityManager()->getConnection()->prepare($rawSql);
$stmt = $conn->executeQuery([$rawSql]);
return $stmt->fetchAll();
}
However this request have some weird behavior specially when i set two different $id variable if i manually set the user id and recipient id it start to duplicate some messages and also the user.login returned is the same for all messages sent and recieved. But still a kind of progress as i have the conversation with this sql request but its not a good solution for long term as the ->fetchAll() method is depreciated and going to be removed from doctrine api in 2023.
By the way the clause UNION in my sql request seem to not have its equal version with DQL do you have any tips to make a UNION with a DQL request ?
I think you are right about adding a conversation table ill have to think back about my db architecture maybe it will make the process more simple and intuitive