Doctrine symfony multiple leftjoin Query

I am trying to run this query and getting error "Unknown relation alias programs". this is the query.

$q= Doctrine_Query::create()
             ->select('students.firstname',
                      'students.middlename',
                      'students.lastname',
                      'programs.program',
                      'courses.title',
                      'programcourses.year')
             ->from('students s, s.programs p, p.programcourses p2, p2.courses c');

I tried this one too.

$q= Doctrine_Query::create()
             ->select('students.firstname',
                      'students.middlename',
                      'students.lastname',
                      'programs.program',
                      'courses.title',
                      'programcourses.year')
             ->from('students')
             ->leftJoin('programs')
             ->leftJoin('programcourses')
             ->leftJoin('courses')
             ->where("idstudents=".$studentid);

This is my Schema.yml.

  Courses:
  connection: doctrine
  tableName: courses
  columns:
    idcourses:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    title:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    Programcourses:
      local: idcourses
      foreign: idcourses
      type: many
Programcourses:
  connection: doctrine
  tableName: programcourses
  columns:
    idprogramcourses:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    idprograms:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    idcourses:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    year:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    Courses:
      local: idcourses
      foreign: idcourses
      type: one
    Programs:
      local: idprograms
      foreign: idprograms
      type: one
Programs:
  connection: doctrine
  tableName: programs
  columns:
    idprograms:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    program:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    Programcourses:
      local: idprograms
      foreign: idprograms
      type: many
    Students:
      local: idprograms
      foreign: idprograms
      type: many
Roles:
  connection: doctrine
  tableName: roles
  columns:
    idroles:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    role:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
Students:
  connection: doctrine
  tableName: students
  columns:
    idstudents:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    firstname:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    middlename:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    lastname:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    idprograms:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    session:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    username:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    password:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    email:
      type: string(255)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    Programs:
      local: idprograms
      foreign: idprograms
      type: one
Teachers:
  connection: doctrine
  tableName: teachers
  columns:
    idteachers:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    firstname:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    lastname:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    username:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    password:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    email:
      type: 开发者_如何学编程string(255)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false

also is there a mysql to dql converter tool? first i make mysql queries and then i change those queries to dql. Is there an easy method?

---

You can easily try your dql using the command line symfony doctrine:dql Now, in your query your relation programs is in lowercase, whereas it is in uppercase in your schema. If I were you, I would try something like this:

$q= Doctrine_Query::create()
             ->select('s.firstname,
                      s.middlename,
                      s.lastname,
                      p.program,
                      c.title,
                      pc.year')
             ->from('Students s')
             ->leftJoin('s.Programs p')
             ->leftJoin('p.Programcourses pc')
             ->leftJoin('pc.Courses')
             ->where("idstudents = ?", $studentid);

---

There was a little mistake in your answer greg, if u can see select function, each column name is separated by comma, it should be like this..

$q= Doctrine_Query::create()
         ->select('s.firstname,
                  s.middlename,
                  s.lastname,
                  p.program,
                  c.title,
                  pc.year')
         ->from('Students s')
         ->leftJoin('s.Programs p')
         ->leftJoin('p.Programcourses pc')
         ->leftJoin('pc.Courses c')
         ->where("idstudents = ".$studentid);