Jump to:

5513 Posts in 1732 Topics by 1218 members

Customising the CMS

SilverStripe Forums » Customising the CMS » Customising Report using Two leftJoins on Member Table

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1194 Views
  • Tama
    Avatar
    Community Member
    130 Posts

    Customising Report using Two leftJoins on Member Table Link to this post

    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

  • Tama
    Avatar
    Community Member
    130 Posts

    Re: Customising Report using Two leftJoins on Member Table Link to this post

    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

  • lanks
    Avatar
    Community Member
    61 Posts

    Re: Customising Report using Two leftJoins on Member Table Link to this post

    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

  • Corry
    Avatar
    Community Member
    17 Posts

    Re: Customising Report using Two leftJoins on Member Table Link to this post

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

  • Corry
    Avatar
    Community Member
    17 Posts

    Re: Customising Report using Two leftJoins on Member Table Link to this post

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

    1194 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.