I am working on a custom Silverstripe report that was created before my time by my current employer. The report returns the CompetitionEntry lines for a specific Competition along with the name of the competition (and a few other fields that I deleted to keep the code fragments in order).
When we tried to export 10,000 or more results, it will be disabled. The general MySQL journal showed that the query was executed every time in each competition for each competition entry:
SELECT DISTINCT Created, LastEdited, URLSegment, Title, MenuTitle, Content, MetaDescription, ExtraMeta, ShowInMenus, ShowInSearch, Sort, HasBrokenFile, HasBrokenLink, ReportClass, CanViewType, CanEditType, Priority, ProvideComments, MetaTitle, CommentNotificationEmail, Version, ParentID, TrackingTagName, CustomQuestion, EnableCustomQuestion, CustomQuestionRequired, TermsAndConditions, SubmittedText, HeaderContent, InactiveContent, BackgroundColour, Active, Image1ID, Image2ID, CASE WHEN ClassName IS NOT NULL THEN ClassName ELSE 'SiteTree' END AS RecordClassName FROM SiteTree_Live LEFT JOIN Page_Live ON Page_Live.ID = SiteTree_Live.ID LEFT JOIN CompetitionPage_Live ON CompetitionPage_Live.ID = SiteTree_Live.ID WHERE (SiteTree_Live.ID = 308) AND (SiteTree_Live.ClassName IN ('CompetitionPage')) ORDER BY Active DESC LIMIT 1
Column Function:
public function columns() { return array( "Competition.Title" => "Competition", "FirstName" => "First name", "LastName" => "Last name", "Email" => "Email" ); }
My getExportFields:
public function getExportFields(){ return array( "Competition.Title" => "Competition", "Date" => "Date", "FirstName" => "FirstName", "LastName" => "LastName", "Email" => "Email", ); }
Commenting on the link to Competition.Title , the request is fast and gives me all the records.
I don’t see how to make the request effective, or if you can just find the competition once, and then add the title (and one or two additional fields of the competition) to each entry in the competition.