CODEWITHSUNDEEP
symfony1doctrine
mysterious
mysterious

Reputation: 1478

symfony doctrine query result and execute function

I have this doctrine query in symfony. It returns a lot of rows when i run mysql code generated by this dql query in phpBB but when i run it in symfony and access its results with this code:

foreach ($this->courses as $course){
 echo "<br>".$course->firstname;}

it returns only one name. Also when i try to get $course->title, this error appears Unknown record property / related component "title" on "Students"

Query:

$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 = ?",2);
$this->courses=$q->execute();

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

Upvotes: 1

Views: 8205

Answers (4)

Rahul Mankar
Rahul Mankar

Reputation: 990

Prepare query like this:

public function getAttendanceRecord($employeeId, $date) {
try {
    $query = Doctrine_Query::create()
            ->from("attendanceRecord")
            ->where("employeeId = ?", $employeeId);
    $records = $query->execute();
    if (is_null($records[0]->getId())) {
        return null;
    } else {
        return $records;
    }
} catch (Exception $ex) {
    throw new DaoException($ex->getMessage());
}

}

And try to get/print like this:

$attendanceOfTheDay = AttendanceDao::getAttendanceRecord($parameters['employeeId'], $parameters['date'])->toArray(); echo ' '; print_r($punchInTImeOfTheDay); exit();

Upvotes: 0

Julien
Julien

Reputation: 1925

Your top node is the a Students object, because you use Students in the from()

Your error describe this

Also when i try to get $course->title, this error appears Unknown record property / related component "title" on "Students"

because you have a Students object, not a Courses one (where title belongs)

You are confusing yourself using

$this->courses=$q->execute();

$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); // beware to SQL injection, use parameters

$this->Student=$q->fectchOne(); // as you are only retrieve one in your where clause
$this->Student=$q->execute()->getFirst(); // same

<tr>
  <td><?php echo $i; /* not sure what this is for */ ?></td>
  <td><?php echo $Student->Programs->Programcourses->Courses->title ?></td>
  <td><?php echo $Student->Programs->Programcourses->year ?></td>
  <td><?php echo $Student->Programs->program ?></td>
</tr>

If you want Courses to be the top, just go for something like:

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

Upvotes: 2

mysterious
mysterious

Reputation: 1478

this is how i get it work...

$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);

//$this->query=$q->getSqlQuery();
$q->setHydrationMode(Doctrine_Core::HYDRATE_SCALAR);
$this->Student=$q->execute(array());

and in template

<?php foreach ($Student as $student): ?>
<tr>
  <td><?php echo $i; ?></td>
  <td><?php echo $student['c_title'] ?></td>
  <td><?php echo $student['pc_year'] ?></td>
  <td><?php echo $student['p_program'] ?></td>
</tr>
<?php endforeach; ?>

Upvotes: 4

Jason Swett
Jason Swett

Reputation: 45074

A few things:

  • Instead of $course->firstname and $course->title, you want $course->getFirstName() and $course->getTitle().
  • You should use singular names for your tables instead of plural, e.g. Course instead of Courses.
  • The convention in symfony is to use "id" for the primary key name instead of what you're doing.

Fix those problems - especially the first one - and your overall problem should go away.

Upvotes: 1

Related Questions