PHP: Doctrine: order joined records

Question

Hi,

I'm new to doctrine: I have a problem with the sorting of joined records.

A sample.

I've got an Article model which is associated with a Source model in 1 <-> n. The source model has a property called 'position' with an integer value.

Now I want to fetch an article with it's sources orderes by the position. My DQL looks like this:

$q = Doctrine_Query::create()
  ->select('a.title, s.content')
  ->from('Article a')
  ->leftJoin('a.Source s')
  ->where('a.id = ?')
  ->orderBy('s.position');

The result doesn't change if I edit the position.

Best regards, Sebastian

Answer 1

Hmm... it should do. Maybe try either of these:

->orderBy('s.position DESC')
->orderBy('s.position ASC')

Answer 2

Yes, it looks OK. Try to generate the SQL from the DQL with getSqlQuery() and query the database with the result. If there is still the wrong output, it might by a problem with the data or more likely, with the DQL.

Answer 3

Perhaps you should include the column that you are using for the ordering (s.position), so try this:

$q = Doctrine_Query::create()
->select('a.title, s.content, s.position')
->from('Article a')
->leftJoin('a.Source s')
->where('a.id = ?')
->orderBy('s.position');