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

OK, I've got somewhere - If I look here: http://svn.silverstripe.com/open/modules/sapphire/branches/2.4/core/model/SQLQuery.php

The leftJoin function reads like this:

/**
	 * Add a LEFT JOIN criteria to the FROM clause.
	 * 
	 * @param String $table Table name (unquoted)
	 * @param String $onPredicate The "ON" SQL fragment in a "LEFT JOIN ... AS ... ON ..." statement.
	 *  Needs to be valid (quoted) SQL.
	 * @param String $tableAlias Optional alias which makes it easier to identify and replace joins later on
	 * @return SQLQuery This instance 
	 */
	public function leftJoin($table, $onPredicate, $tableAlias=null) {
		if( !$tableAlias ) {
			$tableAlias = $table;
		}
		$this->from[$tableAlias] = "LEFT JOIN \"$table\" AS \"$tableAlias\" ON $onPredicate";
		return $this;
	}

But if I look at our SS2.4.0 code it reads like this:

	/**
	 * Add an INNER JOIN criteria to the FROM clause.
	 * 
	 * @return SQLQuery This instance 
	 */
	public function innerJoin($table, $onPredicate) {
		$this->from[$table] = "INNER JOIN \"$table\" ON $onPredicate";
		return $this;
	}

I tracked this back to this change ticket: http://open.silverstripe.org/ticket/5802

We currently have our sapphire + cms tied down to SVN at version 2.4.0. Would upgrading to 2.4.1 add the updated function?

Cheers
Tama

Hi Tama

I am needing to do a same table join as well (the only way I can see to do it is using aliases) but I can't seem to be able to use aliases in a DO::get etc.
Did you ever solve this?

Liam

Not sure if anyone still needs this, but I finally worked out how to get the query working. The table alias is an optional third parameter for the leftjoin() function. Note that the get_title_sql() function also needs the alias name as a parameter.

in function columns, fields aray:

 'AuthorTitle' => 'Author',
 'ApproverTitle' => 'Approver',

in function sourceRecords, adding to the query:

//Add author
$q->leftJoin('Member', '"WorkflowRequest".AuthorID = "M1"."ID"', 'M1');
$q->select[] = Member::get_title_sql('M1').' AS "AuthorTitle"';

//Add approver
$q->leftJoin('Member', '"WorkflowRequest".ApproverID = "M2"."ID"', 'M2');
$q->select[] = Member::get_title_sql('M2').' AS "ApproverTitle"';

Should also mention that I am using SilverStripe 2.4.4 - the table alias isn't available in earlier editions.