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