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)?

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

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.

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 :-)

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

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.