Symfony3 - Left Join does not work properly

Trying to solve this problem with Join in Symfony3.

Somehow the resultset has mixed up values, i.e

ID Exploit Author
ID Author Author

Please see the attached screenshot.

OpenX should have Author Metasploit and the second row shouldn't be there.

Have this code in the controller:

 public function indexAction()
    {
        $exploits = $this->getDoctrine()
                ->getRepository('AppBundle:Exploit')->createQueryBuilder('e')
             ->add('select', 'a,e')
             ->add('from', 'AppBundle:Exploit e')
             ->leftJoin('AppBundle:Author', 'a')
             ->where('e.author = a.id')
             ->getQuery()
             ->getResult();

        return $this->render('exploit/index.html.twig', array(
            'exploits' => $exploits
        ));
    }

This view:

 <tbody>
            {% for exploit in exploits %}
            <tr>
                <th scope="row">{{ exploit.id }}</th>
                <td>{{ exploit.name }}</td>

                <td> {{ exploit.author }} </td>

                <td>
                    <a href="/details/{{ exploit.id }}" class="btn btn-success">View</a>
                    <a href="/edit/{{ exploit.id }}" class="btn btn-default">Edit</a>
                    <a href="/delete/{{ exploit.id }}" class="btn btn-danger">Delete</a>
                </td>
            </tr>
            {% endfor %}

And these Entities:

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Author
 *
 * @ORM\Table(name="author", indexes={@ORM\Index(name="author_name_id_idx", columns={"id"})})
 * @ORM\Entity
 */
class Author
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var \Exploit
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\OneToOne(targetEntity="Exploit")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id", referencedColumnName="author")
     * })
     */
    private $id;



    /**
     * Set name
     *
     * @param string $name
     *
     * @return Author
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    public function getAuthor()
    {
        return $this->name;
    }
}
<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Author
 *
 * @ORM\Table(name="author", indexes={@ORM\Index(name="author_name_id_idx", columns={"id"})})
 * @ORM\Entity
 */
class Author
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var \Exploit
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\OneToOne(targetEntity="Exploit")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id", referencedColumnName="author")
     * })
     */
    private $id;


}

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Category
 *
 * @ORM\Table(name="category", indexes={@ORM\Index(name="category_name_id_idx", columns={"id"})})
 * @ORM\Entity
 */
class Category
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var \Exploit
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\OneToOne(targetEntity="Exploit")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id", referencedColumnName="category")
     * })
     */
    private $id;



    /**
     * Set name
     *
     * @param string $name
     *
     * @return Category
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
}
<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Category
 *
 * @ORM\Table(name="category", indexes={@ORM\Index(name="category_name_id_idx", columns={"id"})})
 * @ORM\Entity
 */
class Category
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var \Exploit
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\OneToOne(targetEntity="Exploit")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id", referencedColumnName="category")
     * })
     */
    private $id;


}

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Exploit
 *
 * @ORM\Table(name="exploit", indexes={@ORM\Index(name="exploit_category_idx", columns={"category"}), @ORM\Index(name="exploit_type_idx", columns={"type"}), @ORM\Index(name="exploit_author_idx", columns={"author"})})
 * @ORM\Entity
 */
class Exploit
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="bigint", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="edb_id", type="string", length=100, nullable=false)
     */
    private $edbId;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date", type="datetime", nullable=false)
     */
    private $date;

    /**
     * @var integer
     *
     * @ORM\Column(name="author", type="bigint", nullable=false)
     */
    private $author;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var integer
     *
     * @ORM\Column(name="category", type="bigint", nullable=false)
     */
    private $category;

    /**
     * @var string
     *
     * @ORM\Column(name="version", type="string", length=255, nullable=false)
     */
    private $version;

    /**
     * @var integer
     *
     * @ORM\Column(name="type", type="bigint", nullable=false)
     */
    private $type;

    /**
     * @var string
     *
     * @ORM\Column(name="content", type="text", nullable=false)
     */
    private $content;

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

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

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



    /**
     * Set edbId
     *
     * @param integer $edbId
     *
     * @return Exploit
     */
    public function setEdbId($edbId)
    {
        $this->edbId = $edbId;

        return $this;
    }

    /**
     * Get edbId
     *
     * @return integer
     */
    public function getEdbId()
    {
        return $this->edbId;
    }

    /**
     * Set date
     *
     * @param \DateTime $date
     *
     * @return Exploit
     */
    public function setDate($date)
    {
        $this->date = $date;

        return $this;
    }

    /**
     * Get date
     *
     * @return \DateTime
     */
    public function getDate()
    {
        return $this->date;
    }

    /**
     * Set author
     *
     * @param integer $author
     *
     * @return Exploit
     */
    public function setAuthor($author)
    {
        $this->author = $author;

        return $this;
    }

    /**
     * Get author
     *
     * @return integer
     */
    public function getAuthor()
    {
        return $this->author;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Exploit
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set category
     *
     * @param integer $category
     *
     * @return Exploit
     */
    public function setCategory($category)
    {
        $this->category = $category;

        return $this;
    }

    /**
     * Get category
     *
     * @return integer
     */
    public function getCategory()
    {
        return $this->category;
    }

    /**
     * Set version
     *
     * @param string $version
     *
     * @return Exploit
     */
    public function setVersion($version)
    {
        $this->version = $version;

        return $this;
    }

    /**
     * Get version
     *
     * @return string
     */
    public function getVersion()
    {
        return $this->version;
    }

    /**
     * Set type
     *
     * @param integer $type
     *
     * @return Exploit
     */
    public function setType($type)
    {
        $this->type = $type;

        return $this;
    }

    /**
     * Get type
     *
     * @return integer
     */
    public function getType()
    {
        return $this->type;
    }

    /**
     * Set content
     *
     * @param string $content
     *
     * @return Exploit
     */
    public function setContent($content)
    {
        $this->content = $content;

        return $this;
    }

    /**
     * Get content
     *
     * @return string
     */
    public function getContent()
    {
        return $this->content;
    }

    /**
     * Set dork
     *
     * @param string $dork
     *
     * @return Exploit
     */
    public function setDork($dork)
    {
        $this->dork = $dork;

        return $this;
    }

    /**
     * Get dork
     *
     * @return string
     */
    public function getDork()
    {
        return $this->dork;
    }

    /**
     * Set softwareLink
     *
     * @param string $softwareLink
     *
     * @return Exploit
     */
    public function setSoftwareLink($softwareLink)
    {
        $this->softwareLink = $softwareLink;

        return $this;
    }

    /**
     * Get softwareLink
     *
     * @return string
     */
    public function getSoftwareLink()
    {
        return $this->softwareLink;
    }

    /**
     * Set testedOn
     *
     * @param string $testedOn
     *
     * @return Exploit
     */
    public function setTestedOn($testedOn)
    {
        $this->testedOn = $testedOn;

        return $this;
    }

    /**
     * Get testedOn
     *
     * @return string
     */
    public function getTestedOn()
    {
        return $this->testedOn;
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
}
<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Exploit
 *
 * @ORM\Table(name="exploit", indexes={@ORM\Index(name="exploit_category_idx", columns={"category"}), @ORM\Index(name="exploit_type_idx", columns={"type"}), @ORM\Index(name="exploit_author_idx", columns={"author"})})
 * @ORM\Entity
 */
class Exploit
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="bigint", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="edb_id", type="string", length=100, nullable=false)
     */
    private $edbId;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date", type="datetime", nullable=false)
     */
    private $date;

    /**
     * @var integer
     *
     * @ORM\Column(name="author", type="bigint", nullable=false)
     */
    private $author;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var integer
     *
     * @ORM\Column(name="category", type="bigint", nullable=false)
     */
    private $category;

    /**
     * @var string
     *
     * @ORM\Column(name="version", type="string", length=255, nullable=false)
     */
    private $version;

    /**
     * @var integer
     *
     * @ORM\Column(name="type", type="bigint", nullable=false)
     */
    private $type;

    /**
     * @var string
     *
     * @ORM\Column(name="content", type="text", nullable=false)
     */
    private $content;

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

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

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


}

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Type
 *
 * @ORM\Table(name="type", indexes={@ORM\Index(name="type_name_id_idx", columns={"id"})})
 * @ORM\Entity
 */
class Type
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var \Exploit
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\OneToOne(targetEntity="Exploit")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id", referencedColumnName="type")
     * })
     */
    private $id;



    /**
     * Set name
     *
     * @param string $name
     *
     * @return Type
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
}
<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Type
 *
 * @ORM\Table(name="type", indexes={@ORM\Index(name="type_name_id_idx", columns={"id"})})
 * @ORM\Entity
 */
class Type
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var \Exploit
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\OneToOne(targetEntity="Exploit")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id", referencedColumnName="type")
     * })
     */
    private $id;


}

Anybody know how to correct my join query ? Or why it behaves like this?

Thanks,

标签： symfony doctrine-orm

2条回答

爱情/是我丢掉的垃圾

2楼-- · 2019-08-22 17:24

Try this:

->leftJoin('AppBundle:Author', 'a', "WITH", "e.author = a.id")

Instead of this:

->leftJoin('AppBundle:Author', 'a')
             ->where('e.author = a.id')

0人赞添加讨论(0) 举报

做个烂人

3楼-- · 2019-08-22 17:50

Try

 public function indexAction()
{
    $exploits = $this->getDoctrine()
         ->getRepository('AppBundle:Exploit')->createQueryBuilder('e')
         ->leftjoin('e.author','a')
         ->addSelect('a')
         ->getQuery()
         ->getResult();

    return $this->render('exploit/index.html.twig', array(
        'exploits' => $exploits
    ));
}

and in the view

<tbody>
            {% for exploit in exploits %}
            <tr>
                <th scope="row">{{ exploit.id }}</th>
                <td>{{ exploit.name }}</td>

                <td> {{ exploit.author.name }} </td>

                <td>
                    <a href="/details/{{ exploit.id }}" class="btn btn-success">View</a>
                    <a href="/edit/{{ exploit.id }}" class="btn btn-default">Edit</a>
                    <a href="/delete/{{ exploit.id }}" class="btn btn-danger">Delete</a>
                </td>
            </tr>
            {% endfor %}
</tbody>

0人赞添加讨论(0) 举报

Symfony3 - Left Join does not work properly

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间