Extracting data from associations in Doctrine 2 and Zend Framework

Programming in sql: Extracting data from associations in Doctrine 2 and Zend Framework on newest questions tagged sql – Stack Overflow

I have a simple entity structure defined in my project:

Country > Region > Province > Town

defined like this :

class Town
{
    /**
     * @Id @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    private $id;

    /** @Column(type="string", length=30,unique=FALSE) */
    private $townname;

    /** @Column(type="boolean") */
    private $active;

    // so that we know when a user has added a town
    /** @Column(type="boolean") */
    private $verified;

    /**
     * @ManyToOne(targetEntity="Province")
     * @JoinColumn(name="provinces_id", referencedColumnName="id",nullable=FALSE)
     */
    private $provinces_id;

etc.

OK. You get the idea.

Now, if a user has a town (many to one) then I would like to know the province, region and country for that user. I could make a repository method for the town entity that provides this data using SQL or DQL. Something like:

SELECT `users`.`towns_id`,`towns`.`provinces_id`,`regions`.`countries_id`,`provinces`.`regions_id` FROM users , `regions`
LEFT JOIN `towns` ON `users`.`towns_id` = `towns`.`id`
LEFT JOIN `provinces` ON `towns`.`provinces_id` = `provinces`.`id`

But this seems wrong, especially since doctrine already ‘knows’ about the relationships between the towns,provinces, regions and countries entities.

Is there an easier way to do this?

source: http://stackoverflow.com/questions/8831883/extracting-data-from-associations-in-doctrine-2-and-zend-framework
Programming in sql: programming-in-sql