Morning All
I'm working on a couple of reports to help us manage our Workflow. One of them is very similar to the RecentlyPublishedPagesReport in the CMS Workflow module.
This report displays the Publisher of each page but I want to be able to add the Author as well. Here is the existing code for the query:
$q = singleton('SiteTree')->extendedSQL();
$q->select[] = '"SiteTree"."Title" AS "PageTitle"';
$q->leftJoin('WorkflowRequest', '"WorkflowRequest"."PageID" = "SiteTree"."ID"');
$q->select[] = "\"WorkflowRequest\".\"LastEdited\" AS \"Published\"";
$q->where[] = "\"WorkflowRequest\".\"ClassName\" = 'WorkflowPublicationRequest'";
$q->where[] = "\"WorkflowRequest\".\"Status\" = 'Completed'";
$q->leftJoin('Member', '"WorkflowRequest"."PublisherID" = "Member"."ID"');
$q->select[] = Member::get_title_sql().' AS "PublisherTitle"';
To add an Author I would then add another leftJoin but I need to assign an alias to the table (Member AS m1, Member AS m2) and do not know how:
$q->leftJoin('Member', '"WorkflowRequest"."AuthorID" = "Member"."ID"');
$q->select[] = Member::get_title_sql().' AS "AuthorTitle"';
What is the correct approach for this? How can I add aliases to tables inside the leftJoin function?
Cheers
Tama