5102 Posts in 1520 Topics by 1116 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 960 Views |
-
Customising Report using Two leftJoins on Member Table
14 September 2010 at 9:10am
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 -
Re: Customising Report using Two leftJoins on Member Table
15 September 2010 at 2:35pm Last edited: 15 September 2010 2:53pm
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 -
Re: Customising Report using Two leftJoins on Member Table
12 January 2011 at 10:59pm
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
-
Re: Customising Report using Two leftJoins on Member Table
13 February 2012 at 12:14am
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"'; -
Re: Customising Report using Two leftJoins on Member Table
13 February 2012 at 12:16am
Should also mention that I am using SilverStripe 2.4.4 - the table alias isn't available in earlier editions.
| 960 Views | ||
|
Page:
1
|
Go to Top |
