"Relations Master Detail", have problemm, Help

March 18, 2022, 10:32:33 PM

Greetings.
I have a problem that drives me crazy. I have a table called "agenda" that has a relationship with another table "historial", that relationship is of the Master Detail type.
Pressing should show all those histoiral related to that patient. That is what I wish.
and this is what appears:

Error 500
Server Error
Exception Traces
This will only be displayed in DEVELOPMENT_MODE.
Error Message
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ID_Paciente' in where clause is ambiguous
File
D:\WEB\htdocs\agenew\app\models\PDODb.php On Line 1045
Stack Trace
1 D:\WEB\htdocs\agenew\app\models\PDODb.php(1045): PDOStatement->execute()
...

THE Query
SELECT SQL_CALC_FOUND_ROWS historial.ID_HIST, pacientes.FOTO_Pac AS pacientes_FOTO_Pac, historial.Nro_Historia, CONCAT(pacientes.Nombre,' ',pacientes.Apellido) AS NomApePac, historial.ID_Paciente, CONCAT(medico_tratante.Nombre,' ',medico_tratante.Apellido) AS NomApeMed, historial.Fecha, historial.Motivo, historial.Tratamiento, historial.Resultado, historial.Notas, historial.Honorarios, historial.Cancelados FROM historial INNER JOIN pacientes ON historial.ID_Paciente = pacientes.ID_Paciente INNER JOIN medico_tratante ON historial.ID_Medico_Tratante = medico_tratante.ID_MedTrat INNER JOIN procedimiento ON historial.id_Proc = procedimiento.ID_Procedimiento WHERE ID_Paciente = 4 ORDER BY historial.ID_HIST DESC LIMIT 20 OFFSET 0

and you are right, it is ambiguous, there are many "ID_Paciente".

26 / 5.000
Resultados de traducción
The correct query is this:
SELECT SQL_CALC_FOUND_ROWS historial.ID_HIST, pacientes.FOTO_Pac AS pacientes_FOTO_Pac, historial.Nro_Historia, CONCAT(pacientes.Nombre,' ',pacientes.Apellido) AS NomApePac, historial.ID_Paciente, CONCAT(medico_tratante.Nombre,' ',medico_tratante.Apellido) AS NomApeMed, historial.Fecha, historial.Motivo, historial.Tratamiento, historial.Resultado, historial.Notas, historial.Honorarios, historial.Cancelados FROM historial INNER JOIN pacientes ON historial.ID_Paciente = pacientes.ID_Paciente INNER JOIN medico_tratante ON historial.ID_Medico_Tratante = medico_tratante.ID_MedTrat INNER JOIN procedimiento ON historial.id_Proc = procedimiento.ID_Procedimiento WHERE historial.ID_Paciente = 4 ORDER BY historial.ID_HIST DESC LIMIT 20 OFFSET 0

This is de SELECT for agenda
SELECT * FROM agenda
INNER JOIN medico_tratante ON agenda.id_MedTrat=medico_tratante.ID_MedTrat
INNER JOIN pacientes ON agenda.Nro_Historia=pacientes.Nro_Historia

and this for historial
SELECT * FROM historial
INNER JOIN pacientes ON historial.ID_Paciente=pacientes.ID_Paciente
INNER JOIN medico_tratante ON historial.ID_Medico_Tratante=medico_tratante.ID_MedTrat
INNER JOIN procedimiento ON historial.id_Proc=procedimiento.ID_Procedimiento

After a step by step DEBUG of the program I have found a controller, HistoriaController.php, the complete information is not passed.
In this line I check the content of the variable and only "Patient_ID" appears, when making the comparison, the generated SQL reads like this:

I modify line 66 in the HistoriaController, and add a reference to the "historial" table.
from this:
66 $db->where($fieldname , $fieldvalue); //filter by a single field name

to this:
66 $db->where($tablename.".".$fieldname , $fieldvalue); //filter by a single field name

Now it works, but every time I do a PREVIEW, and the files are updated and the modification is lost.
I am using PHPRad Clasic V 2.7.3.
I listen to your opinions and advice. For my part I keep a notebook of the points that I must modify in the final version