Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Customising the CMS /

Customising Report using Two leftJoins on Member Table


Reply


5 Posts   1283 Views

Avatar
Tama

Community Member, 132 Posts

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

Avatar
Tama

Community Member, 132 Posts

15 September 2010 at 2:35pm

Edited: 15/09/2010 2:53pm

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

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: [url]http://open.silverstripe.org/ticket/5802[/url]

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

Avatar
lanks

Community Member, 61 Posts

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

Avatar
Corry

Community Member, 17 Posts

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"';

Avatar
Corry

Community Member, 17 Posts

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.