Doctrine 1.2 : joining userprofile to city and city to country in DQL

Question

I'm using Doctrine 1.2 with Symfony 1.4 to write queries. I understood the principles of DQL and I have several queries that works fine. But for some needs, I don't find the solution in DQL and I'm using SQL instead.

Here is a simple example : let's say that you have in your schema.yml a sfguarduserprofile class with a relation to a city class (using a city_id in the profile). And that you have a relation between the city class and a country class (using a country_id in the city class).

I'm simply trying to write a DQL query to get the user profile with the name of the city and the name of the country. seems pretty simple ?

Yes, but I didn't find a "clean" solution in DQL because I don't have a direct alias or relation between the sfguarduserprofile and the country class.

I could create a direct relation between my sfguarduserprofile and my country class but I will not respect anymore the best practices of modelization (MERISE).

I could use a select from my City class and get the userprofile and the country (City has relations with these two tables), but I can't use this solution because I have other joins to do from my sfguarduserprofile.

In SQL, it's very easy but I would like to use the doctrine objects.

If you have a "clean" solution, I will be very happy to know it !

thank you

Sandrino

Answer 1

I found the solution, it was simple but it didn't work in the past because my schema.yml was not optimized for Doctrine. Here is an example of a working query :

 $q = Doctrine_Query::create()
        ->select('p.user_id, u.username, c.name, co.name')
        ->from('sfGuardUserProfile p')
        ->leftJoin('p.User u')
        ->leftJoin('p.City c')
        ->leftJoin('c.Country co')
        ->orderBy('p.user_id');