Related Tables query not working

Schema & Data
1

I have a Table Appointment

The Appointment table has the following feilds:-

  1. Id
  2. Patient_Id (Foreign Key of Patient Table)/Can be Null as well
  3. Blocked Flag (If true, then we can block the time slot without a valid patient id).
  4. Time Slot

I have another table named Patient, which is linked with the Appointment table thru the Patient_Id column:-

  1. Patient_Id
  2. Name
  3. Mobile

Now, please note that in the above scenarios, I can have rows in the Appointment table, where there may not be any patient_id, so that row is not linked with the patient table.

The issue is that while fetching rows from the Appointment Table with the parameter “related=patient_by_patient_id”, the query fails. This only happens if even one of the rows returned in the query has no patient_id.

As per my understanding, this should not return and error. If we have a row then it should return me the related data, otherwise ignore.

The constraint is as below:-
ALTER TABLE appointment
ADD CONSTRAINT patient_appointment_fk FOREIGN KEY (patient_id)
REFERENCES patient (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;

Please let me know if you consider it a bug?
I have a work-around to insert a dummy row in the patient table with an patient_id=0, but I am more keen on knowing what is the expected behavior that you folks think.

2

@praveen This does indeed sound like a bug. It should return the queried records, adding related only where available. Could you please let me know what version of the DSP you are running, what type of SQL DB you are connected to, and what the exact error is returned for the call?

3

@leehicks
version: 1.8.0 Bitnani installed version on Amazon AWS
DB: PostGreSQL (Remote SQL DB)
Error:
“error”: [
{
“context”: null,
“message”: “Failed to retrieve records from ‘appointment’.\nCDbCommand failed to execute the SQL statement: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: “”\nLINE 3: WHERE “id” = ‘’ LIMIT 1000\n ^”,
“code”: 500
}
]

This error vanishes as soon as I link the appointment table with the patient table using patient_id.

4

@praveen This was a bug (particularly for PostgreSQL syntax that is more strict than MySQL). I put a fix in our develop branch. If you want to make that change locally, it should work for you. Otherwise, we will hopefully have a lib-php-common-platform release out soon, that you can pull with composer update. I will post back here when it is released.