时间戳

首页 > 解决方案 > 教义：如何从带有表连接的查询中返回数据？

symfony - 教义：如何从带有表连接的查询中返回数据？

问题描述

我有两张桌子，votes和voters。表连接如下：voters.id = votes.voter_id

该votes表包含投票时的 IP 地址。

我想通过voters表中的IP地址选择表中的所有记录和IP地址votes。我该怎么做呢？Votes该方法是否应该存储在Voter存储库中？

我收到一条错误消息： Attempted to load class "Join" from namespace "App\Repository\Expr". Did you forget a "use" statement for "Doctrine\ORM\Query\Expr\Join"?

我已经包含了这条线use Doctrine\ORM\Query\Expr\Join;，但它没有被使用。

对于注释掉的leftjoin我得到这个错误：

[Semantical Error] line 0, col 52 near 'voters WHERE': Error: Class App\Entity\Votes has no association named voters

对于leftjoin，我收到此错误：

[Syntax Error] line 0, col 66: Error: Expected end of string, got 'ON'

到目前为止我有这个，但它不太正确：

public function findByIP($value)
{
    return $this->createQueryBuilder('v')
        ->andWhere('votes.ipaddress = :ipaddress')
        ->setParameter('ipaddress', $value)
        //->leftJoin('v.voters', 'voters')
        ->leftJoin('App\Entity\Voter', 'voter', Join::ON, 'voter.id = v.voter_id')
        ->getQuery()
        ->execute();
}

选民实体：

<?php

namespace App\Entity;

use App\Repository\VoterRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=VoterRepository::class)
 * @ORM\Table(name="voters")
 */
class Voter
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $email;

    /**
     * @ORM\Column(type="string", length=32)
     */
    private $password;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $address;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $address_line_2;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $town;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $city;

    /**
     * @ORM\Column(type="string", length=10, nullable=true)
     */
    private $postcode;

    /**
     * @ORM\Column(type="bigint")
     */
    private $nec_number;

    /**
     * @ORM\Column(type="date", nullable=true)
     */
    private $dateofbirth;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $first_name;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $last_name;

    /**
     * @ORM\OneToMany(targetEntity=Votes::class, mappedBy="voter")
     */
    private $votes;

    public function __construct()
    {
        $this->votes = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getEmail(): ?string
    {
        return $this->email;
    }

    public function setEmail(string $email): self
    {
        $this->email = $email;

        return $this;
    }

    public function getPassword(): ?string
    {
        return $this->password;
    }

    public function setPassword(string $password): self
    {
        $this->password = $password;

        return $this;
    }

    public function getAddress(): ?string
    {
        return $this->address;
    }

    public function setAddress(?string $address): self
    {
        $this->address = $address;

        return $this;
    }

    public function getAddressLine2(): ?string
    {
        return $this->address_line_2;
    }

    public function setAddressLine2(?string $address_line_2): self
    {
        $this->address_line_2 = $address_line_2;

        return $this;
    }

    public function getTown(): ?string
    {
        return $this->town;
    }

    public function setTown(?string $town): self
    {
        $this->town = $town;

        return $this;
    }

    public function getCity(): ?string
    {
        return $this->city;
    }

    public function setCity(?string $city): self
    {
        $this->city = $city;

        return $this;
    }

    public function getPostcode(): ?string
    {
        return $this->postcode;
    }

    public function setPostcode(?string $postcode): self
    {
        $this->postcode = $postcode;

        return $this;
    }

    public function getNecNumber(): ?string
    {
        return $this->nec_number;
    }

    public function setNecNumber(string $nec_number): self
    {
        $this->nec_number = $nec_number;

        return $this;
    }

    public function getDateofbirth(): ?\DateTimeInterface
    {
        return $this->dateofbirth;
    }

    public function setDateofbirth(?\DateTimeInterface $dateofbirth): self
    {
        $this->dateofbirth = $dateofbirth;

        return $this;
    }

    public function getFirstName(): ?string
    {
        return $this->first_name;
    }

    public function setFirstName(string $first_name): self
    {
        $this->first_name = $first_name;

        return $this;
    }

    public function getLastName(): ?string
    {
        return $this->last_name;
    }

    public function setLastName(string $last_name): self
    {
        $this->last_name = $last_name;

        return $this;
    }

    /**
     * @return Collection|Votes[]
     */
    public function getVotes(): Collection
    {
        return $this->votes;
    }

    public function addVote(Votes $vote): self
    {
        if (!$this->votes->contains($vote)) {
            $this->votes[] = $vote;
            $vote->setVoter($this);
        }

        return $this;
    }

    public function removeVote(Votes $vote): self
    {
        if ($this->votes->removeElement($vote)) {
            // set the owning side to null (unless already changed)
            if ($vote->getVoter() === $this) {
                $vote->setVoter(null);
            }
        }

        return $this;
    }
}

投票实体：

<?php

namespace App\Entity;

use App\Repository\VotesRepository;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=VotesRepository::class)
 * @ORM\Table(name="votes")
 */
class Votes
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    private $vote;

        /**
     * @ORM\Column(type="string", length=20, nullable=true)
     */
    private $ipaddress;

    /**
     * @ORM\Column(type="bigint")
     */
    private $nec_number;

    /**
     * @ORM\ManyToOne(targetEntity=Voter::class, inversedBy="votes")
     */
    private $voter;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getVote(): ?int
    {
        return $this->vote;
    }

    public function setVote(?int $vote): self
    {
        $this->vote = $vote;

        return $this;
    }

    public function getIpaddress(): ?string
    {
        return $this->ipaddress;
    }

    public function setIpaddress(?string $ipaddress): self
    {
        $this->ipaddress = $ipaddress;

        return $this;
    }

    public function getNecNumber(): ?string
    {
        return $this->nec_number;
    }

    public function setNecNumber(string $nec_number): self
    {
        $this->nec_number = $nec_number;

        return $this;
    }

    public function getVoter(): ?Voter
    {
        return $this->voter;
    }

    public function setVoter(?Voter $voter): self
    {
        $this->voter = $voter;

        return $this;
    }
}

标签： symfonydoctrine-orm

解决方案

我想通了。

在VotesRepository.php添加此方法：

public function findByIP($value)
{
    return $this->createQueryBuilder('v')
        ->innerJoin('v.voter','voter')
        ->addSelect('voter') //eager loading
        ->andWhere('v.ipaddress = :ipaddress')
        ->setParameter('ipaddress', $value)
        ->getQuery()
        ->execute()
        ;
}

在树枝模板中添加：

<ul>
    {% for vote in votes %}
        <li>{{ vote.getIpaddress() }}</li>
        <li>{{ vote.getVoter().getEmail() }}</li>
    {% endfor %}
</ul>

推荐阅读