how to solve the conflict of "column not found" on symfony 1.4 using doctrine?

Question

Sorry for this very specific question, Ive got this problem couple of times, and my solution (not the best was rename tables and do more stuffs), obviously its not a real solution, this time i got into same situation again, with a table I am not even manipulating, I wonder if someone got the same problem before.

Working with symfony 1.4 and doctrine, I get this error from a query of a not related table

Column not found: 1054 Unknown column 'o.training_id' in 'field list'

I really have no idea whats wrong, at my schema.yml i have something like this

OhrmTraining:
  connection: doctrine
  tableName: training
  columns:
    id_training:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    name:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
TrainingDetail:
  connection: doctrine
  tableName: training_detail
  columns:
    id_training_detail:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    training:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
   relations:
    OhrmTraining:
    local: training
    foreign: id_training
    type: one

Its related with other table Detail

like this

TrainingDetail:
  connection: doctrine
  tableName: training_detail
  columns:
    id_training_deetail:
    type: integer(4)
    fixed: false
    unsigned: false
    primary: true
    autoincrement: true
  training:
    type: integer(4)
    fixed: false
    unsigned: false
    primary: false
    notnull: true
    autoincrement: false
relations:
  Training:
  local: training
  foreign: id_training
  type: one

Which I think its correct, but at my doctrine/base/BaseTraining.class I have something like this at the end (for training)

public function setUp()
{
    parent::setUp();
    $this->hasMany('TrainingDetail', array(
         'local' => 'id', <--- this should be the id_training_detail??
         'foreign' => 'training_id')); <--- this should be training?

I dont know why they are like that, or if they should be like that... could anybody help me with this?

I changed them for

public function setUp()
{
    parent::setUp();
    $this->hasMany('TrainingDetail', array(
         'local' => 'id_training',
         'foreign' => 'training'));

Still, I get the error, and if I make a remake models all will be gone

There is no easy way to create the schema.yml file from a DB?

EDIT: the query that throws the exception is this

 $query = Doctrine_Query::create()
    ->from('Times a')
    ->where('a.employee_id = ?', $employeeId)
    ->orderBy('a.start_date ASC');
    $results = $query->execute();

I checked on the base of employee, and it has no relation with training, it has with training details... but not directly with training, i recreated my models 10 times and still the same

Answer 1

"Still, I get the error, and if I make a remake models all will be gone"

This is because you edited the setup via a base class, base classes ALWAYS get overidden when you build models.

"There is no easy way to create the schema.yml file from a DB?"

This doesn't make sense. It's obvious you were able to generate a schema file from the schema, otherwise you wouldn't have a schema file.

The location of the relations appears correct according to the given schema file. According to Doctrine's docs:

"When specifying relationships it is only necessary to specify the relationship on the end where the foreign key exists. When the schema file is parsed, it reflects the relationship and builds the opposite end automatically. If you specify the other end of the relationship manually, the auto generation will have no effect."

When you build the model files, doctrine will correctly configure the setUp method in the base classes, so you don't need to do it. If you want to change the relationship, change it via the schema file then rebuild the model files.

I suspect the problem you are having is in how you are constructing the query, either via DQL or model references, due to the error you posted:

"Column not found: 1054 Unknown column 'o.training_id' in 'field list'"

-- Edit --

Based on the error, and your query, you could specify the columns to select, try this:

$query = Doctrine_Query::create()
    ->select('a.*')
    ->from('Times a')
    ->where('a.employee_id = ?', $employeeId)
    ->orderBy('a.start_date ASC');

$results = $query->execute();