vtiger

[hammer]

I have a client that needs notes sorted by createdtime under the notes and attachements tab, in the Sales Order module. I know SQL fairly well but this one has me stumped. How do I modify the query below to get the results to sort by the crmentity.createdtime field. Putting in "ORDER BY crmentity.createdtime" does not work, neither does "ORDER BY crm2.createdtime".

Code:
SELECT notes.title, 'Notes 'ActivityType, notes.filename, attachments.type FileType, crm2.modifiedtime lastmodified, seattachmentsrel.attachmentsid attachmentsid, notes.notesid crmid
FROM notes
INNER JOIN senotesrel ON senotesrel.notesid = notes.notesid
INNER JOIN crmentity ON crmentity.crmid = senotesrel.crmid
INNER JOIN crmentity crm2 ON crm2.crmid = notes.notesid
AND crm2.deleted =0
LEFT JOIN seattachmentsrel ON seattachmentsrel.crmid = notes.notesid
LEFT JOIN attachments ON seattachmentsrel.attachmentsid = attachments.attachmentsid
WHERE crmentity.crmid =9309
UNION ALL SELECT attachments.description title, 'Attachments'ActivityType, attachments.name filename, attachments.type FileType, crm2.modifiedtime lastmodified, attachments.attachmentsid attachmentsid, seattachmentsrel.attachmentsid crmid
FROM attachments
INNER JOIN seattachmentsrel ON seattachmentsrel.attachmentsid = attachments.attachmentsid
INNER JOIN crmentity ON crmentity.crmid = seattachmentsrel.crmid
INNER JOIN crmentity crm2 ON crm2.crmid = attachments.attachmentsid
WHERE crmentity.crmid =9309


This query is in the get_attachements method of the saleorder class.

[Bushwack]

That query gets manipulated before being executed, so your changes aren't having any effect.

To add sorting by note creation time just open the file modules/Notes/Note.php and add "createdtime" to the $sortby_fields array.

[hammer]

Actually I found the solution. It was in the account class. There is a method to get associated notes for a company record which contains the original query.

as I found out, in order to use 'ORDER BY' with a 'UNION' statement you must put both select queries in parenthises and include the field you wish to use for sorting in the field list for each. You then can put the 'ORDER BY' clause at the end but you can't use any table names. Use an alias in the field list and then refer to that alias.

Code:
//The original code
function get_attachments($id)
{
   $query = "select notes.title,'Notes      '  ActivityType, notes.filename, attachments.type  FileType,crm2.modifiedtime  lastmodified, seattachmentsrel.attachmentsid attachmentsid, notes.notesid crmid from notes inner join senotesrel on senotesrel.notesid= notes.notesid inner join crmentity on crmentity.crmid= senotesrel.crmid inner join crmentity crm2 on crm2.crmid=notes.notesid and crm2.deleted=0 left join seattachmentsrel  on seattachmentsrel.crmid =notes.notesid left join attachments on seattachmentsrel.attachmentsid = attachments.attachmentsid where crmentity.crmid=".$id;
   $query .= ' union all ';
   $query .= "select attachments.description  title ,'Attachments'  ActivityType, attachments.name  filename, attachments.type  FileType, crm2.modifiedtime  lastmodified, attachments.attachmentsid  attachmentsid, seattachmentsrel.attachmentsid crmid from attachments inner join seattachmentsrel on seattachmentsrel.attachmentsid= attachments.attachmentsid inner join crmentity on crmentity.crmid= seattachmentsrel.crmid inner join crmentity crm2 on crm2.crmid=attachments.attachmentsid where crmentity.crmid=".$id;
   renderRelatedAttachments($query,$id);
}


Code:
//new code
function get_attachments($id)
{
   $query = "(select notes.title,'Notes      '  ActivityType, notes.filename, attachments.type  FileType,crm2.createdtime created,crm2.modifiedtime  lastmodified, seattachmentsrel.attachmentsid attachmentsid, notes.notesid crmid from notes inner join senotesrel on senotesrel.notesid= notes.notesid inner join crmentity on crmentity.crmid= senotesrel.crmid inner join crmentity crm2 on crm2.crmid=notes.notesid and crm2.deleted=0 left join seattachmentsrel  on seattachmentsrel.crmid =notes.notesid left join attachments on seattachmentsrel.attachmentsid = attachments.attachmentsid where crmentity.crmid=".$id." ORDER BY crm2.createdtime DESC)";
   $query .= ' union all ';
   $query .= "(select attachments.description  title ,'Attachments'  ActivityType, attachments.name  filename, attachments.type  FileType,crm2.createdtime created, crm2.modifiedtime  lastmodified, attachments.attachmentsid  attachmentsid, seattachmentsrel.attachmentsid crmid from attachments inner join seattachmentsrel on seattachmentsrel.attachmentsid= attachments.attachmentsid inner join crmentity on crmentity.crmid= seattachmentsrel.crmid inner join crmentity crm2 on crm2.crmid=attachments.attachmentsid where crmentity.crmid=".$id." ORDER BY crm2.createdtime DESC)";
   $query .= " ORDER BY created DESC";
   renderRelatedAttachments($query,$id);
}


This will order the notes by created date in a company record. The sort fields you refer to only affect the main list view for a module.