Symfony & Doctrine getting a joined query to work

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.

The working query is:

SELECT c.* FROM country c INNER JOIN ducks d ON c.id = d.country_id GROUP BY c.country ORDER BY c.country ASC

I've tried converting this to:

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

which produces the following error:

[Semantical Error] line 0, col 79 near 'd ON': Error: Identification Variable WfukDuckBundle:Ducks used in join path expression but was not defined before.

I'm quite new to Symfony/Doctrine so I suspect it's probably something obvious!

I'm using Symfony 2.0.11 with doctrine

Update: I've Also tried:

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

where 'ducks' is defined in the Country class as:

/** * @ORM\OneToMany(targetEntity="Ducks", mappedBy="country") */ protected $ducks; public function __construct() { $this->ducks = new ArrayCollection(); }

the definition for country in the ducks class is:

/** * @ORM\ManyToOne(targetEntity="Country", inversedBy="ducks") * @ORM\JoinColumn(name="country_id", referencedColumnName="id") */ private $country;

-------------Problems Reply------------

Do yourself a favour and use the query builder. Easier to read and update and reuse your queries

<?php

namespace Vendor\Prefix\Repository;

use Doctrine\ORM\EntityRepository;

class SomeRepository extends EntityRepository
{
public function countryDucks()
{
// $em is the entity manager
$qb = $em->createQueryBuilder();

$qb
->select('country', 'duck')
->from('WfukDuckBundle:Country', 'country')
->innerJoin('country.ducks', 'duck')
->groupBy('country.country')
->orderBy('country.country', 'ASC')
;

$query = $qb->getQuery();

// Potential Hydration Modes
// --------------------------------
// Doctrine\ORM\Query::HYDRATE_OBJECT
// Will give you an array of your object entities
// --------------------------------
// Doctrine\ORM\Query::HYDRATE_ARRAY
// Will give you an array mimicking
// your object graph
// --------------------------------

return $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
}
}

Category:php Views:0 Time:2012-03-05

Related post

  • symfony doctrine three tables one query to Pager (Pagination)? 2011-01-29

    I have three tables: Table 1: Images ------------------------ ID | user_id | title ------------------------ 1 | 0 |Image 1 2 | 1 |Image 2 Table 2: Images_Upload (image_id = Table Images ID) ------------------------ ID | image_id | file --------------

  • Symfony Propel: Join Query Across different Database 2009-08-28

    I have two tables that should be joined together by a foreign key relationship, unfortunately they are separate into two different database. My question is that is it possible for me to create a Criteria thingy that allows me to create cross database

  • How do i detach a behavior in Symfony/Doctrine? 2009-09-24

    I have doctrine's softdelete behavior attached to all of my models. Is there a way I can hard delete a particular record? In cakephp I remember detaching the behavior... deleting the record and then re attaching the behavior. Is there something simil

  • In symfony/doctrine's schema.yml, where should I put onDelete: CASCADE for a many-to-many relationship? 2009-09-28

    I have a many-to-many relationship defined in my Symfony (using doctrine) project between Orders and Upgrades (an Order can be associated with zero or more Upgrades, and an Upgrade can apply to zero or more Orders). # schema.yml Order: columns: order

  • symfony doctrine build-sql error 2010-04-10

    I have some big problems with symfony and doctrine at the beginning of a new project. I have created database diagram with mysql workbench, inserted the sql into phpmyadmin and then I've tried symfony doctrine:build-schema to generate the YAML schema

  • symfony + doctrine + inheritance, how to make them work? 2010-05-05

    I am beginning to work with Symfony, I've found some documentation about inheritance. But also found this discouraging article, which make me doubt if Doctrine handles inheritance any good at all... Has anyone find a smart solution for inheritance in

  • Doctrine2 many-to-one association won't use JOIN query 2010-11-12

    I have a Car entity with a many-to-one relationship with an entity Owner. If I select all cars, Doctrine does one query on the Car table, and subsequently one query on the Owner table for each car. So fetching N cars becomes N+1 queries instead of a

  • Symfony+Doctrine: Correct YAML syntax for default value of boolean field 2010-11-21

    I am working on the schema for my Symfony app, and I need to set the default value of two boolean fields to false. However, with all the ways I've tried to do it, when the sql gets generated, it comes out with the default keyword, but no default valu

  • Symfony doctrine i18n behavior data-load failure: Integrity constraint violation 2011-01-14

    I want tho have I18N categories table. I have followed the jobeet example. Schema and fixtures data below. ./symfony doctrine:build --db --all-classes --and-migrate ./symfony doctrine:data-load data/fixtures/category.yml When I run these commands no

  • Symfony/Doctrine: fetching data as object 2011-03-18

    In Symfony, I use Doctrine_Query to query the database $q = Doctrine_Query::create() ->from('User u') ->where('u.username = ?', $username) ->andWhere('u.password = ?', $password); $user = $q->fetchArray(); The problem is that the results

  • Symfony doctrine::build task cannot create table named order 2011-04-25

    I have a database with a table named order. When i run php symfony doctrine:build --all, i got the folowing error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your

  • sql syntax error while symfony doctrine:generate-module command 2011-11-18

    So, my problem is.. i`m trying to execute this command: php symfony doctrine:generate-module --env="my_env" --non-verbose-templates backend city_theme CityTheme After a pause, i get this error: SQLSTATE[42000]: Syntax error or access violation: 1064

  • How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table? 2008-10-06

    I have two tables in my database, called ratings and movies. Ratings: | id | movie_id | rating | Movies: | id | title | A typical movie record might be like this: | 4 | Cloverfield (2008) | and there may be several rating records for Cloverfield, lik

  • How to create a dataview In Sharepoint with data from a join query? 2009-01-22

    I have 3 Lists in Sharepoint. I want to create a dataview that is a join of 3 tables. Table1 is joined with Table2 on FieldA Table 2 is joined to Table3 on FieldB Table1 has duplicate values in FieldA so I need to only return one value to join with T

  • Join query or subquery 2009-03-18

    Are there rules of thumb for developers when to use join instead of subquery or are they the same. --------------Solutions------------- Depends on RDBMS. You should compare execution plans for both queries. In my experience with Oracle 10 and 11, exe

  • a JOIN query edited so that a field that has unique values 2009-05-06

    I have a JOIN query that pulls the last 6 "topic" records (the records with the highest topic_ids that are have a topic_status of 0, i.e. it's not spam): SELECT topic_title, topic_slug, meta_value, topic_poster FROM `folio_topics` as Topics INNER JOI

  • Django .."join" query? 2009-07-31

    guys, how or where is the "join" query in Django? i think that Django dont have "join"..but how ill make join? Thanks --------------Solutions------------- If you're using models, the select_related method will return the object for any foreign keys y

  • unit testing with symfony + doctrine + PHPUnit 2009-09-10

    I'm working with Symfony + Doctrine + PHPUnit, with NetBeans IDE. Here' my current approach to unit testing. setUp() function loads the test fixtures from .yml files tearDown() function delete all data from models. this is done by looping through an

  • how to resolve symfony doctrine:build-schema error (Unknown relation alias table_name) 2009-10-26

    how to resolve this symfony error : C:\inetpub\wwwroot\project\trunk\preprod\signup>php symfony doctrine:build-schema --trace >> doctrine generating yaml schema from database [sfException] Unknown relation alias table_name Exception trace: a

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.139 (s). 11 q(s)