Cakephp 4: Help with Excel Export from Search Engine

Need Help
1

I use a search plugin in my app that includes an option to export the search results to Excel. I’ve been asked to update the export with some values that come from a joined table, so I modified the contain statement to include DocRevisions:

$query = $this->Docs->find('search', [
        'search' => $this->request->getQueryParams()]
        )
    ->contain(
        ['Categories', 
        'Projects', 
        'Discrepancies', 
        'Systems', 
        'Author', 
        'Initiator',
        'DocRevisions' => [
            'RevStatus',
            'RevAuthor',
            'EngApprov',
            'ModifiedUser',
            'AppTech',
            ],
        ]
    ) 
    ->order('Docs.doc_no ASC');
    // Call to exportSearch() at end of search() method
    $this->exportSearch($query);

In my app, Docs hasMany DocRevisions. Some docs have none; others have more.

In my exportSearch method, I have an $extract variable set up like this. It works on the $query array I passed it from search():

$extract = [
            'doc_no',
            'current_rev_no',
            function (array $row) {
                return $row['project']['location'];
            },
            function (array $row) {
                return $row['discrepancy']['description'];
            },
            function (array $row) {
                return $row['author']['full_name'];
            },
            function (array $row) {
                return $row['doc_revisions']['rev_status']['description'];
            },
        ];

My problem is with the last function, the one that tries to grab the description of the revision status. Every time I try to export it, I get Undefined index: rev_status. Yet, when I debug, I can see the values I want:

debug($row['doc_revisions']);
'rev_status' => [
    'id' => (int) 1,
    'description' => 'Engineering Review',
    'display_order' => (int) 1,
],

This is also true of the other contains I defined (modified_user, eng_approv, rev_author). They’re all there when I debug $row. But each one produces the same Undefined index result when I try to export them. How can I get these values to appear in my export?

2

Is rev_status_id in your error message a typo? There’s nothing in the code you’ve shared that references that, so either it’s a typo in your post or the error is coming from somewhere else.

3

Sorry, yes. Typo. That’s the foreign key in DocRevisions that points to RevStatus. If I try this:

$row['doc_revisions']['rev_status']['description']

I get Undefined index: doc_revisions. If I try this:

$row['rev_status']['description']

I get Undefined index: rev_status.

4

Okay, that’s better. :slight_smile: There’s definitely something missing, though, from your output of debug($row['doc_revisions']);. I’m guessing that it’s not an array of fields, but rather an array of arrays of fields? And that what you’d need is more $row['doc_revisions'][0]['rev_status']['description'].

1 Like
5

You are correct, sir. Once I added an index, the revision status description showed up. Thanks!

Now, a more complex, but related, question: Suppose a doc has many revisions, but I only want the revision that has rev_status_id != 6 (in my app, that would be the “active”, not “closed”, revision) in my export. How can I get that index in my current setup? I’m guessing I’ll want to assign it to a variable so that I get $row['doc_revisions'][$index]['rev_status']['description']; Not sure how to get there, though.

UPDATE: Think I figured that out, too:

function (array $row) {
    foreach($row['doc_revisions'] as $r => $rr) {
        if ($rr['rev_status_id'] != 6) {
            $index = $r;
            return $row['doc_revisions'][$index]['rev_status']['description'];
        } 
    }
}

Seems to be working correctly so far.