How to add "o.idempresa" to the DQL query?

Question

I'm trying to build this query:

SELECT 
    re . *, t.tipo, m.patente, o.nombre, de.revisado
FROM
    sdriving_registros_emisores re
        LEFT JOIN
    sdriving_turno t ON re.idturno = t.idturno
        LEFT JOIN
    sdriving_maquina_emisor me ON me.idmaquinaemisor = re.maquinaemisorid
        LEFT JOIN
    sdriving_maquina m ON me.idmaquina = m.idmaquina
        LEFT JOIN
    sdriving_operador o ON re.idoperador = o.idoperador
        LEFT JOIN
    sdriving_detalle_emisores de ON de.idregistros = re.idregistros
WHERE
    o.idempresa = 1

using Doctrine DQL. This is the code I made:

Doctrine_Core::getTable('SdrivingRegistrosEmisores')
                ->createQuery('re')
                ->leftJoin('re.SdrivingTurno t')
                ->leftJoin('re.SdrivingMaquinaEmisor me')
                ->leftJoin('me.SdrivingMaquina m')
                ->leftJoin('re.SdrivingOperador o')
                ->leftJoin('re.SdrivingDetalleEmisores de')
                ->execute();

But if I add where('o.idempresa', $id_empresa) I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'o.idempresa' in 'where clause'

What's the proper way to do this? What I miss?

EDIT Added the schema.yml file:

SdrivingDetalleEmisores:
  connection: doctrine
  tableName: sdriving_detalle_emisores
  actAs: [Timestampable]
  columns:
    iddetalle_emisores:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    idregistros:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    fecha_registro:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    dertalle_mensaje:
      type: string()
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    estado:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    revisado:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    modo:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    remitente:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    SdrivingRegistrosEmisores:
      local: idregistros
      foreign: idregistros
      type: one
SdrivingEmisor:
  connection: doctrine
  tableName: sdriving_emisor
  actAs: [Timestampable]
  columns:
    idemisor:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    idempresa:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    numero:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    SdrivingEmpresa:
      local: idempresa
      foreign: idempresa
      type: one
    SdrivingMaquinaEmisor:
      local: idemisor
      foreign: idemisor
      type: many
SdrivingEmpresa:
  connection: doctrine
  tableName: sdriving_empresa
  columns:
    idempresa:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: true
    idlogotipo:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    nombre_empresa:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    ruta_emp:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    SdrivingLogotipo:
      local: idlogotipo
      foreign: idlogotipo
      type: one
    SdrivingEmisor:
      local: idempresa
      foreign: idempresa
      type: many
    SdrivingMaquina:
      local: idempresa
      foreign: idempresa
      type: many
    SdrivingOperador:
      local: idempresa
      foreign: idempresa
      type: many
    SdrivingTurno:
      local: idempresa
      foreign: idempresa
      type: many
    SfGuardUserProfile:
      local: idempresa
      foreign: idempresa
      type: many
SdrivingLogotipo:
  connection: doctrine
  tableName: sdriving_logotipo
  columns:
    idlogotipo:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: true
    archivo:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    SdrivingEmpresa:
      local: idlogotipo
      foreign: idlogotipo
      type: many
SdrivingMaquina:
  connection: doctrine
  tableName: sdriving_maquina
  actAs: [Timestampable]
  columns:
    idmaquina: { type: integer(8), fixed: false, unsigned: false, primary: true, autoincrement: true }
    idempresa: { type: integer(4), fixed: false, unsigned: true, primary: true, autoincrement: false }
    patente: { type: string(12), fixed: false, unsigned: false, primary: false, notnull: true, autoincrement: false }
  relations:
    Empresa:
      local: idempresa
      class: SdrivingEmpresa
      type: one
      foreignType: one
      foreignAlias: MaquinaEmpresa
      onDelete: CASCADE
      onUpdate: CASCADE
    Emisor:
      local: idmaquina
      class: SdrivingMaquinaEmisor
      type: many
      foreignType: many
      foreignAlias: MaquinaEmisor
      onDelete: CASCADE
      onUpdate: CASCADE
SdrivingMaquinaEmisor:
  connection: doctrine
  tableName: sdriving_maquina_emisor
  actAs: [Timestampable]
  columns:
    idmaquinaemisor:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    idmaquina:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    idemisor:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
  relations:
    SdrivingEmisor:
      local: idemisor
      foreign: idemisor
      type: one
    SdrivingMaquina:
      local: idmaquina
      foreign: idmaquina
      type: one
    SdrivingRegistrosEmisores:
      local: idmaquinaemisor
      foreign: maquinaemisorid
      type: many
SdrivingOperador:
  connection: doctrine
  tableName: sdriving_operador
  actAs: [Timestampable]
  columns:
    idoperador:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    idempresa:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    nombre:
      type: string(255)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    rut:
      type: string(12)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    SdrivingEmpresa:
      local: idempresa
      foreign: idempresa
      type: one
    SdrivingRegistrosEmisores:
      local: idoperador
      foreign: idoperador
      type: many
SdrivingRegistrosEmisores:
  connection: doctrine
  tableName: sdriving_registros_emisores
  actAs: [Timestampable]
  columns:
    idregistros:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    maquinaemisorid:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    idoperador:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    idturno:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
  relations:
    SdrivingTurno:
      local: idturno
      foreign: idturno
      type: one
    SdrivingOperador:
      local: idoperador
      foreign: idoperador
      type: one
    SdrivingMaquinaEmisor:
      local: maquinaemisorid
      foreign: idmaquinaemisor
      type: one
    SdrivingDetalleEmisores:
      local: idregistros
      foreign: idregistros
      type: many
SdrivingTurno:
  connection: doctrine
  tableName: sdriving_turno
  actAs: [Timestampable]
  columns:
    idturno:
      type: integer(8)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    idempresa:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    tipo:
      type: string(30)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    SdrivingEmpresa:
      local: idempresa
      foreign: idempresa
      type: one
    SdrivingRegistrosEmisores:
      local: idturno
      foreign: idturno
      type: many

SfGuardUserProfile:
  connection: doctrine
  tableName: sf_guard_user_profile
  columns:
    id: { type: integer(8), primary: true }
    user_id: { type: integer(8), primary: false }
    idempresa: { type: integer(4), primary: false }
  relations:
    User:
      local: user_id
      class: sfGuardUser
      type: one
      foreignType: one
      foreignAlias: SfGuardUserProfile
      onDelete: CASCADE
      onUpdate: CASCADE

What if you add the `where` and instead of `execute`, call `getSqlQuery(array($id_empresa))` to see the sql query that Doctrine will generate. It might help to debug. — j0k, Jun 14 '13 at 14:58
@j0k I've found the problem. I added the parameter in this way `where('idempresa = ?', $id_empresa)` and it works but now I have another problem. How I can access to joins results as a object meaning for example to show in a template? — Reynier, Jun 14 '13 at 15:14
Once you're in your `foreach($results as $result)`, try `$result->getSdrivingTurno()->getId()`. — j0k, Jun 14 '13 at 17:57
@j0k You've a type is `$result->getSdrivingTurno->getId()` not as you said but anyway thanks. In the other side I'm doing something wrong here. See based on the same query I'm trying to access `getEstado()` which is a method for `SdrivingDetalleEmisores` but the only code that works is this: `$record->SdrivingDetalleEmisores->SdrivingRegistrosEmisores->getRevisado()` which I think is wrong because never return the value .I try already this `$record->SdrivingDetalleEmisores->getRevisado()` — Reynier, Jun 14 '13 at 20:31
@j0k but then I get this error **Warning: call_user_func_array() expects parameter 1 to be a valid callback, class 'Doctrine_Collection' does not have a method 'getRevisado' in /var/www/html/monitor/lib/vendor/symfony/lib/escaper/sfOutputEscaperObjectDecorator.class.php on line 64** what I'm doing wrong? Also could you please answer this as answer and not as comment in order to give you the correct answer? — Reynier, Jun 14 '13 at 20:32
The problem is that `SdrivingRegistrosEmisores` can have many `SdrivingDetalleEmisores` that why it returns you a `Doctrine_Collection`. Because you have many one associated. If you want the first one, you can do: `$record->SdrivingDetalleEmisores->getFirst()->getRevisado()` — j0k, Jun 15 '13 at 10:27
@j0k but inside the foreach? I tried the code inside the foreach — Reynier, Jun 15 '13 at 13:23
@j0k the idea with that field is to get the value foreach associated record so if it gets `NULL` then I do a action, if gets `0` I do other action and if it gets `1` I do another one, the only possible values are `0` and `1` because NULL if when it doesn't exists in `SdrivingDetalleEmisores` — Reynier, Jun 15 '13 at 13:43
@j0k ok I figure out how to work with your suggestion but now when no records related exists on `SdrivingDetalleEmisores` I got this error `Fatal error: Call to a member function getRevisado() on a non-object in /var/www/html/monitor/apps/frontend/modules/dashboard/templates/indexSuccess.php on line 23` how I deal with this? I mean how do I check if any related record exists on `SdrivingDetalleEmisores` table? — Reynier, Jun 15 '13 at 16:31
Something like `if (count($record->SdrivingDetalleEmisores) > 0)` — j0k, Jun 16 '13 at 19:29

How to add "o.idempresa" to the DQL query?

0 Answers0