Databases
StackOverflow2013
Postsdbo
POSymfony & Doctrine getting a joined query to work
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSymfony & Doctrine getting a joined query to work
    primarykey
    data
    text
    <p>I've got an SQL query that returns all the rows in one table (country) which have a related entry in another table (ducks) but I'm struggling to turn this into DQL. This is a standard one-many relationship as each country can have multiple ducks, I believe it is all set up correctly as I can return ducks within a country and return the country a duck is in using standard code. </p> <p>The working query is:</p> <pre><code>SELECT c.* FROM country c INNER JOIN ducks d ON c.id = d.country_id GROUP BY c.country ORDER BY c.country ASC </code></pre> <p>I've tried converting this to:</p> <pre><code>SELECT c FROM WfukDuckBundle:Country c INNER JOIN WfukDuckBundle:Ducks d ON c.id = d.country_id GROUP BY c.country ORDER BY c.country ASC </code></pre> <p>which produces the following error:</p> <pre><code>[Semantical Error] line 0, col 79 near 'd ON': Error: Identification Variable WfukDuckBundle:Ducks used in join path expression but was not defined before. </code></pre> <p>I'm quite new to Symfony/Doctrine so I suspect it's probably something obvious!</p> <p>I'm using Symfony 2.0.11 with doctrine</p> <p>Update: I've Also tried:</p> <pre><code>SELECT c FROM WfukDuckBundle:Country c INNER JOIN c.ducks d ON c.id = d.country_id GROUP BY c.country ORDER BY c.country ASC </code></pre> <p>where 'ducks' is defined in the Country class as:</p> <pre><code>/** * @ORM\OneToMany(targetEntity="Ducks", mappedBy="country") */ protected $ducks; public function __construct() { $this-&gt;ducks = new ArrayCollection(); } </code></pre> <p>the definition for country in the ducks class is:</p> <pre><code>/** * @ORM\ManyToOne(targetEntity="Country", inversedBy="ducks") * @ORM\JoinColumn(name="country_id", referencedColumnName="id") */ private $country; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USj0k
    1. USAl_
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSymfony & Doctrine getting a joined query to work
      1. USmirk
      1. VTFavorite
    1. COThe error seems to tell that the entity WfukDuckBundle:Ducks is not defined in the EntityManager.. The exception is thrown from Doctrine\ORM\Query\Parser.php, method: JoinAssociationPathExpression (l. 930) : you can start debugging from there.
      singulars
      1. POSymfony & Doctrine getting a joined query to work
      1. USAlterPHP
    2. COAnd your ON clause doesn't look DQL compatible : "ON c.id = d.country"
      singulars
      1. POSymfony & Doctrine getting a joined query to work
      1. USAlterPHP
    3. COThanks, you're right the JOIN was wrong, there was no need for the ON clause, slowly getting the hang of Doctrine. For anyone else the query should have been: `SELECT c FROM WfukDuckBundle:Country c INNER JOIN c.ducks d GROUP BY c.country ORDER BY c.country ASC`
      singulars
      1. POSymfony & Doctrine getting a joined query to work
      1. USAl_
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload