SQL error when a contact detail view is opened

šŸ‘Øā€šŸ’» Development Help
1

In my v8 version instance, there is an error logged when I open a contact detail view. No error is on the UI but in the log file (suitecrm.log).

SELECT  emails.`date_sent_received`   FROM notes   
LEFT JOIN  contacts contacts ON notes.contact_id=contacts.id AND contacts.deleted=0 AND contacts.deleted=0  
LEFT JOIN  users jt1 ON notes.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 
INNER JOIN  contacts notes_rel ON notes.contact_id=notes_rel.id AND notes_rel.deleted=0
where ( notes.contact_id='768607bc-83ad-11ef-8040-78726430b17c') AND notes.deleted=0   
ORDER BY  emails.`date_sent_received` DESC LIMIT 1

MySQL error 1054: Unknown column ā€˜emails.date_sent_receivedā€™ in ā€˜field listā€™. Has anyone faced the same issue in version 8. Thanks!

1 Like
2

That ā€œFROMā€ is clearly wrong. It seems to be a part of the ContactsLastTouchPoint statisticā€¦ but I donā€™t know why itā€™s brokenā€¦

1 Like
3

Itā€™s in 8.7 fresh installation.

4

Maybe you donā€™t need those quotes. Check where is code for it.

5

Hi,
The issues seems to be in subpanel definition in Notes Module. Can you please check subpanel definition file

modules/Notes/metadata/subpanels/ForHistory.php

. The Query has no join to EMAILS table and the select statement and order by statement is referring to a column belonging to emails table.

6

The issue is fixed by changing the indexes of $queries in ContactLastTouchPoint in 8.7 similar to 8.6.2.

$parts = $queries[5];
.....
$parts = $queries[6];

There were indexes difference in this file in 8.6.2 and 8.7 . The $queries list has different indexes 8.7 and 8.6.2 in ContactLastTouchPoint.php but the order of subpanels in subpaneldefs.php in two versions is not different.

8.7
Core\core\modules\Contacts\Statistics\ContactLastTouchPoint.php
$queries[4] & $queries[5]

        $parts = $queries[4];
        $parts['select'] = 'SELECT  emails.`date_sent_received` ';
        $parts['order_by'] = ' ORDER BY  emails.`date_sent_received` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult1 = $this->fetchRow($innerQuery);

        $parts = $queries[5];
        $parts['select'] = 'SELECT  emails.`date_sent_received` as `emails_date_sent` ';
        $parts['order_by'] = ' ORDER BY  `emails_date_sent` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult2 = $this->fetchRow($innerQuery);

8.6.2
core\modules\Contacts\Statistics\ContactLastTouchPoint.php
$queries[5] & $queries[6]

        $parts = $queries[5];
        $parts['select'] = 'SELECT  emails.`date_sent_received` ';
        $parts['order_by'] = ' ORDER BY  emails.`date_sent_received` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult1 = $this->fetchRow($innerQuery);

        $parts = $queries[6];
        $parts['select'] = 'SELECT  emails.`date_sent_received` as `emails_date_sent` ';
        $parts['order_by'] = ' ORDER BY  `emails_date_sent` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult2 = $this->fetchRow($innerQuery);
1 Like
7

Nice detective work! :man_detective:

Can you open a PR on Github with this fix?

8

Thanks. It looks this change was made for fixing a bug in 8.7. Raised a new issue for this.