Jump to:

23371 Posts in 18161 Topics by 2865 members

General Questions

SilverStripe Forums » General Questions » dataObjectSet doesn't return

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

Page: 1
Go to End
Author Topic: 602 Views
  • johnofjack
    Avatar
    Community Member
    22 Posts

    dataObjectSet doesn't return Link to this post

    I have several many-to-many relationships set up between different tables: articles to books and books to authors.

    I'm trying to pull in articles about authors on the authors' pages, but if I use the control Books and then control Articles, it lists some articles several times (an article might be about several books by the same author).

    After working on that a bit it occurred to me that it wasn't writing the SQL I needed, and SELECT DISTINCT wasn't what I needed: first, it's selecting distinct books then joining articles, so sometimes the same article repeats; and second, if there are several different articles about the same book it would only grab one of them. So I decided to write my own SQL to do what I want and have verified that it works.

    I'm having trouble getting the resultset back to my authors page. Any ideas what I'm doing wrong?

    SQL (this works):
    SELECT DISTINCT articlepage.Date AS Date, sitetree.Title as Title,
    sitetree.URLsegment AS Link from articlepage
    INNER JOIN articlepage_books
    ON articlepage_books.ArticlePageID=articlepage.ID
    INNER JOIN bookpage
    ON articlepage_books.BookPageID=bookpage.ID
    INNER JOIN authorpage_books
    ON bookpage.ID=authorpage_books.BookPageID
    INNER JOIN sitetree
    ON sitetree.ID = articlepage.ID
    INNER JOIN authorpage
    ON authorpage.ID=authorpage_books.AuthorPageID
    WHERE authorpage.ID='222'

    function in AuthorPage.php:
       function authorArticles() {
          $authorID = $this->ID;
          return $authorID;
          
          $sqlQuery = new SQLQuery();      
          $sqlQuery = "SELECT DISTINCT articlepage.Date AS Date, "
          . "sitetree.Title AS Title, sitetree.URLsegment AS Link "
          . "from articlepage "
          . "INNER JOIN articlepage_books "
          . "ON articlepage_books.ArticlePageID=articlepage.ID "
          . "INNER JOIN bookpage "
          . "ON articlepage_books.BookPageID=bookpage.ID "
          . "INNER JOIN authorpage_books "
          . "ON bookpage.ID=authorpage_books.BookPageID "
          . "INNER JOIN sitetree "
          . "ON sitetree.ID = articlepage.ID "
          . "INNER JOIN authorpage "
          . "ON authorpage.ID=authorpage_books.AuthorPageID "
          . "WHERE authorpage.ID='". convert::raw2sql($authorID) . "'";
          
          $result = $sqlQuery->execute();
          // var_dump($result->first()); // array
          
          // let Silverstripe work the magic
          $myDataObjectSet = buildDataObjectSet($result);
          return $myDataObjectSet;
          
          $myFirstStory = $myDataObjectSet->First();
          $Date = $myFirstStory->Date;
          $Title = $myFirstStory->Title;
          $Link = $myFirstStory->Link;
    }

    In AuthorPage.ss, control AuthorArticles works but $myDataObjectSet, $myFirstStory, $Date, $Title, and $Link are all empty.

    Is the function in AuthorPage.php correct (and, therefore, the problem in AuthorPage.ss)?

  • martimiz
    Avatar
    Forum Moderator
    1067 Posts

    Re: dataObjectSet doesn't return Link to this post

    Hi johnofjack

    I don't think that you can add raw SQL to a SQLQuery object like that, you'd need to set its select, where, from, etc arrays.

    See: http://doc.silverstripe.org/sapphire/en/reference/sqlquery

    Also I think you could do this by some function like: foreach $this->Books() merge $this->Articles() into $allArticles, return $allArticles->removeDuplicates(). But that loop might create a lot of overhead if the number of books grows...

  • johnofjack
    Avatar
    Community Member
    22 Posts

    Re: dataObjectSet doesn't return Link to this post

    Thanks, martimiz.

    I did read that page before posting, and failed to find in there how to do what I wanted. I've tried a number of different syntaxes and they all haven't worked (including the first syntax listed, with the option $sqlQuery->distinct = true; )

    The documentation doesn't look particularly good to me--though it's infinitely superior to the documentation for processwire, which is why I switched.

    It turns out the value of the function in my first post is being returned as the id of the author, so the sql isn't working in silverstripe, probably for the reason you mentioned.

    I'll experiment with this some more.

  • martimiz
    Avatar
    Forum Moderator
    1067 Posts

    Re: dataObjectSet doesn't return Link to this post

    One other thing: once you get SQLQuery to return some results, just doing $myDataObjectSet = buildDataObjectSet($result); will not work: buildDataObjectSet() is not a regular PHP function, but a DataObject method. So you need to instantiate a DataObject first, and have it do the work, like maybe:

    $myDataObjectSet = singleton('DataObject')->buildDataObjectSet($result);

    I'm not sure singleton('ArticlePage') would work in this case, but if so - even better! It's in the documentation I mentioned, although that doesn't go out of its way to explain the why of everything. But there's a lot of hard work being done on the docs lately, so they will only improve in time

    Also I see you are using ArticlePage as well articlepage. Maybe on a Windows(?) machine this is allowed, I don't know, but it's a good idea to be very consistent in using the correct case...

    Best of luck

  • johnofjack
    Avatar
    Community Member
    22 Posts

    Re: dataObjectSet doesn't return Link to this post

    I've just discovered Director::set_environment_type("dev");

    It seems the documentation is, in some places, actually wrong: e.g. at http://api.silverstripe.org/2.4/sapphire/model/SQLQuery.html it shows the first parameter under INNER JOIN as "String $table - Table name (unquoted)."

    Leaving the table name unquoted results in an error: [Notice] Use of undefined constant articlepage_books - assumed 'articlepage_books'

    I've barely even used SQL in the last five years, but it took me literally about two minutes to figure out the SQL to pull the info I needed.

    I've already spent over 5 hours trying to get this SQL to work in SilverStripe....

  • johnofjack
    Avatar
    Community Member
    22 Posts

    Re: dataObjectSet doesn't return Link to this post

    Got it sorted, finally.

    My PHP is rusty, to say the least. http://php.net/manual/en/functions.returning-values.php could have explained why it was returning authorID--calling 'return' ends the function's execution.

    It must be frustrating for you lot sometimes, trying to help people in over their heads.

    602 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.