All geek questions in one place

CakePHP 3 is compliant and does not contain a return association

I am using CakePHP 3.3.9 and trying to use the friendsofcake/search+ pagination plugin to filter the list AssessmentLogsbased on Clients.EmrSystemswhich EmrSystemsassignToMany association is on Clients. This is all in the SQL Server database, if that matters.

My problem is that when I use matching()and contain(), I get the correct results, but the connection is Clientnot in the record AssessmentLog, although it is explicitly contained. I am not interested in getting EmrSystemsfor Clientunder each AssessmentLog, but only Clientto whom it belongs.

The created query even seems to be correct, but ORM has only zero association value Clientfor each record. Even _matchingDatacontains Clientaccording to DebugKit, so I know that there is the right information. Manual execution of the generated query even returns the correct results.

This is how associations form:

AssessmentLogsTable

// AssessmentLog belongs to a Client using ClientId field
$this->belongsTo('Clients', [
    'foreignKey' => 'ClientId'
]);

// Search plugin
$this->searchManager()->add('EmrSystem', 'Search.Callback', [
    'callback' => function ($query, $args, $manager) {
        if (!is_array($args['EmrSystem'])) {
            return false;
        }

        // Not returning the Client association for some reason :(
        // Should return only assessment logs where the client has a specified EMR system.
        // The AssessmentLog should always contain the Client association

        return $query->contain([
            'Clients',
            'Clients.EmrSystems'
        ])
        ->matching('Clients.EmrSystems', function ($q) use ($args) {
            return $q->where(function ($exp) use ($args) {
                return $exp->in('EmrSystems.ID', $args['EmrSystem']);
            });
        });
    },
    'filterEmpty' => true
]);

ClientsTable

// Client has many assessment logs - The ID fields aren't named consistently and wasn't my choice or design. The field name is correct.
$this->hasMany('AssessmentLogs', [
    'foreignKey' => 'ClientID'
]);

// Client can have multiple EMR (Electronic Medical Record) systems
$this->belongsToMany('EmrSystems', [
    'joinTable' => 'ClientEmrSystem',
    'foreignKey' => 'ClientId',
    'targetForeignKey' => 'EmrSystemId',
    'through' => 'ClientEmrSystems',
    'saveStrategy' => 'replace'
]);

AssessmentLogsController

// Load other associations
$this->paginate['contain'] = [
    'AssessmentTypes' => function ($q) {
        return $q->select([
            'AssessmentTypes.AssessmentTypeCd',
            'AssessmentTypes.AssessmentTypeShort'
        ]);
    },
    'Clients' => function ($q) {
        return $q->select([
            'Clients.ClientId',
            'Clients.OrganizationName'
        ]);
    },
    'Patients' => function ($q) {
        return $q->select([
            'Patients.PatientId',
            'Patients.FirstName',
            'Patients.LastName'
        ]);
    }   
];

// Use Search Plugin
$assessmentLogs = $this->AssessmentLogs->find(
    'search', 
    $this->AssessmentLogs->filterParams($this->request->query)
);

$this->set('assessmentLogs', $this->paginate($assessmentLogs));

Generated request

I have included some other associations that work correctly and return patient names, types, etc.

SELECT 
  AssessmentLogs.AssessmentLogId AS [AssessmentLogs__AssessmentLogId], 
  AssessmentLogs.ClientID AS [AssessmentLogs__ClientID], 
  AssessmentLogs.PatientID AS [AssessmentLogs__PatientID], 
  AssessmentLogs.AssessmentTypeCd AS [AssessmentLogs__AssessmentTypeCd], 
  Clients.ClientId AS [Clients__ClientId], 
  Clients.OrganizationName AS [Clients__OrganizationName], 
  ClientEmrSystems.ClientId AS [ClientEmrSystems__ClientId], 
  ClientEmrSystems.EmrSystemId AS [ClientEmrSystems__EmrSystemId], 
  ClientEmrSystems.Created AS [ClientEmrSystems__Created], 
  ClientEmrSystems.Modified AS [ClientEmrSystems__Modified], 
  EmrSystems.ID AS [EmrSystems__ID], 
  EmrSystems.Name AS [EmrSystems__Name], 
  EmrSystems.Created AS [EmrSystems__Created], 
  EmrSystems.Modified AS [EmrSystems__Modified], 
  AssessmentTypes.AssessmentTypeCd AS [AssessmentTypes__AssessmentTypeCd], 
  AssessmentTypes.AssessmentTypeShort AS [AssessmentTypes__AssessmentTypeShort], 
  Patients.PatientId AS [Patients__PatientId], 
  Patients.FirstName AS [Patients__FirstName], 
  Patients.LastName AS [Patients__LastName]
FROM 
  AssessmentLog AssessmentLogs 
  INNER JOIN Client Clients ON Clients.ClientId = (AssessmentLogs.ClientId) 
  INNER JOIN ClientEmrSystem ClientEmrSystems ON Clients.ClientId = (ClientEmrSystems.ClientId) 
  INNER JOIN EmrSystem EmrSystems ON EmrSystems.ID = (ClientEmrSystems.EmrSystemId) 
  LEFT JOIN AssessmentType AssessmentTypes ON AssessmentTypes.AssessmentTypeCd = (
    AssessmentLogs.AssessmentTypeCd
  ) 
  LEFT JOIN Patient Patients ON Patients.PatientId = (AssessmentLogs.PatientId) 
WHERE 
  (
    EmrSystems.ID in (1) 
    AND (
      AssessmentLogs.Void = 0 
    )
  ) 
ORDER BY 
  AssessmentLogs.AssessmentLogId OFFSET 0 ROWS FETCH FIRST 40 ROWS ONLY

Circuit example

CREATE TABLE [dbo].[Client](
    [ClientId] [int] NOT NULL,
    [OrganizationName] [varchar](max) NULL,
    [Email] [varchar](max) NULL,
    [WorkPhone] [varchar](max) NULL,
    [Fax] [varchar](max) NULL,
    [UpdatedDate] [datetime] NULL,
    [Notes] [text] NULL,
    CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED 
    (
        [ClientId] ASC
    )
);

CREATE TABLE [dbo].[AssessmentLog](
    [AssessmentLogId] [int] IDENTITY(1,1) NOT NULL,
    [ClientID] [int] NOT NULL,
    [PatientID] [int] NOT NULL,
    [AssessmentTypeCd] [int] NOT NULL,
    [Note] [varchar](max) NULL,
    [Void] [bit] NOT NULL,
    [DateInserted] [datetime] NOT NULL,
    [DateCharged] [datetime] NULL,
    CONSTRAINT [PK_AssessmentLog] PRIMARY KEY CLUSTERED 
    (
        [AssessmentLogId] ASC
    )
);

CREATE TABLE [dbo].[Patient](
    [PatientId] [int] IDENTITY(1,1) NOT NULL,
    [ClientId] [int] NOT NULL,
    [FirstName] [varchar](max) NOT NULL,
    [LastName] [varchar](max) NOT NULL,
    [MedicalRecordNbr] [varchar](max) NOT NULL,
    CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED 
    (
        [PatientId] ASC
    )
);

CREATE TABLE [dbo].[AssessmentType](
    [AssessmentTypeCd] [int] IDENTITY(1,1) NOT NULL,
    [AssessmentTypeShort] [varchar](50) NULL,
    [AssessmentTypeLong] [varchar](max) NULL,
    CONSTRAINT [PK_AssessmentType] PRIMARY KEY CLUSTERED 
    (
        [AssessmentTypeCd] ASC
    )
);

CREATE TABLE [dbo].[EmrSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Created] [datetime2](7) NULL,
    [Modified] [datetime2](7) NULL,
    CONSTRAINT [PK_EmrSystem] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
);

CREATE TABLE [dbo].[ClientEmrSystem](
    [ClientId] [int] NOT NULL,
    [EmrSystemId] [int] NOT NULL,
    [Created] [datetime2](7) NULL,
    [Modified] [datetime2](7) NULL,
    CONSTRAINT [PK_ClientEmrSystem] PRIMARY KEY CLUSTERED 
    (
        [ClientId] ASC,
        [EmrSystemId] ASC
    )
);

Record Examples

Here are sample entries. I have specified primary keys, even if they are auto-increment / identity columns just for simplicity:

INSERT INTO [dbo].[Client] (
    [ClientId],
    [OrganizationName],
    [Email],
    [WorkPhone],
    [Fax],
    [UpdatedDate],
    [Notes],
) VALUES (
    123,
    'Sample Client',
    'sample@sample.com',
    '(555) 555-1234',
    '(555) 555-5678',
    '2016-12-12 12:00:00',
    'Sample notes about sample client.'
);

INSERT INTO [dbo].[Patient] (
    [PatientId],
    [ClientId], 
    [FirstName],
    [LastName],
    [MedicalRecordNbr]
) VALUES (
    1,
    123,
    'Some',
    'Dude',
    'A12345'
);

INSERT INTO [dbo].[AssessmentType] (
    [AssessmentTypeCd],
    [AssessmentTypeShort],  
    [AssessmentTypeLong]
) VALUES (
    1,
    'Sample',
    'Sample Chart'
);

INSERT INTO [dbo].[EmrSystem] (
    [ID],
    [Name], 
    [Created],
    [Modified]
) VALUES (
    1,
    'Some System',
    GETDATE(),
    GETDATE()
);

INSERT INTO [dbo].[ClientEmrSystem] (
    [ClientId], 
    [EmrSystemId],
    [Created],
    [Modified]
) VALUES (
    123,
    1,
    GETDATE(),
    GETDATE()
);  

INSERT INTO [dbo].[AssessmentLog] (
    [ClientID],
    [PatientID],
    [AssessmentTypeCd],
    [Note],
    [Void],
    [DateInserted],
    [DateCharged]
) VALUES (
    123,
    1,
    1,
    'Sample notes',
    0,
    GETDATE(),
    NULL
);

, EMR Client . . contains [], , .

, - , , . :) .

+4

:

437
?
10
/?

Source: https://habr.com/ru/post/1662860/

More articles:


All Articles