CakePHP 3 matching and contain not returning association

Question

I'm using CakePHP 3.3.9 and trying to use the friendsofcake/search plugin + pagination to filter a list of AssessmentLogs based on Clients.EmrSystems, in which EmrSystems is a belongsToMany association on Clients. This is all on a SQL Server database if it makes a difference.

My problem is that when I use matching() and contain() I receive the correct results but the Client association is missing from the AssessmentLog record, even though it is explicitly contained. I'm not concerned about getting the EmrSystems for a Client under each AssessmentLog, only the Client that owns it.

The query generated even appears correct, but the ORM just has a null value for the client association on each record. Even _matchingData contains client according to DebugKit, so I know the right info is there. Manually running the generated query even returns the right results.

Here's how the associations are laid out:

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 Query

I've included some other associations that are working correctly and returning 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

Oddly, when using the EMR System filter, the fields under the Client association are not limited either. It returns everything. I tried adjusting the contain[] array to include the limited fields, but everything is ignored.

I'm assuming I did something wrong and its not Cake's fault, but I can't seem to figure out an elegant solution. Thank you for any help :) It is greatly appreciated.


Show source
| cakephp   | orm   | query-builder   | search   | cakephp-3.3   2016-12-05 17:12 0 Answers

Answers to CakePHP 3 matching and contain not returning association ( 0 )

Leave a reply to - CakePHP 3 matching and contain not returning association

◀ Go back