CREATE VIEW `trainingdetail` AS 
select `training_detail`.`id` as `id`
  ,`training`.`id` AS `training_id`
  ,`training`.`startdate` AS `startdate`
  ,`training`.`enddate` AS `enddate`
  ,concat(`training_detail`.`fname`,' ',`training_detail`.`lname`) AS `fullname` 
from `training_detail`  
 left join `training`  on ((`training`.`id`=`training_detail`.`training_id_p`))
where `training_detail`.`id` is NOT NULL 

I can create this view no issue. but when i try to convert to ORM objects i get this in propel

 Column "id" declared twice in table "trainingdetail"

Both Training_detail and Training table has id column

===========================================================

who can solve it?

CodePudding user response：

Use training_detail.training_id_p column in your view definition instead of training.id. The former is the foreign key and has s different underlying name if that is what confuses propel.

2 additional suggestions:

1. Your where clause does not make any sense as training_detail.id field I expect to be the pk of training_detail table and training_detail table is on the left side of the left join. So, this column cannot be null in this query.

2. I would expect the training table to be on the left hand side in an outer join as it is the parent table. You should have a fk on training_detail.training_id_p preventing it to be pointing to an invalid training record. The field may be nullable, but a training detsil without a training does not make any sense.

CodePudding user response：

In the following line:

left join training on ((training.id=training_detail.training_id_p))

what does it mean "training_detail.training_id_p".

In the table of training_detail the name of the column is id

you mentioned it: select training_detail.id as id

so here you changed the name of the column.

why?

use the same name.