How to sort fields of a related (joined) table in query results?

Question

Probably related more to SQL than to Dotrine itself.

I have two tables, specified in schema file roughly like this:

Project:
  columns:
    name: { type: string(255), notnull: true }

Task:
  columns:
    project_id: { type: integer, notnull: true }
    name: { type: string(255), notnull: true }
  relations:
    Project: { onDelete: CASCADE, local: project_id, foreign: id, foreignAlias: Tasks }

I would like to get a project with a list of it's tasks, sorted by the name.

$projectWithTasks = Doctrine_Core::getTable("Project")->createQuery("p")
    ->leftJoin("p.Tasks t")
    ->where("p.id = ?", $projectId)
    ->orderBy("t.name ASC")
    ->fetchOne();

Obviously, it won't work. I was searching for a solution for quite some time, but probably I am using the wrong words, because I couldn't find any useful information. I would be very grateful for any help.

Answer 1

You can define the default order for Tasks as relation of Projects in your YML file, e.g.:

Project:
  columns:
    name: { type: string(255), notnull: true }
  relations:
    Task:
      local: id
      foreign: project_id
      orderBy: name

Task:
  columns:
    project_id: { type: integer, notnull: true }
    name: { type: string(255), notnull: true }

This way your tasks are automatically sorted by name when gotten through Project.

Answer 2

IF you want to use the selector "t" in your query, you should define it to the select method:

$projectWithTasks = Doctrine_Query::create()
     ->select('p.*, t.*')
     ->from('Project p')
     ->leftJoin('p.Tasks t')
     ->where('p.id = ?', $projectId)
     ->orderBy('t.name ASC')
     ->fetchOne();

Alternatively you can use your syntax, just replace the "t" in orderBy by "p.Tasks":

$projectWithTasks = Doctrine_Core::getTable("Project")->createQuery("p")
    ->leftJoin("p.Tasks t")
    ->where("p.id = ?", $projectId)
    ->orderBy("p.Tasks.name ASC")
    ->fetchOne();