MrBenn

Are any forum moderators around? If so, any chance you could approve my forum registration and initial post please (user: BenH)? Sorry to hassle…

MrBenn

Does anyone know if it is possible when creating a custom SS SQLSelect to prevent the framework from adding a into the fields, as in “Select , field 1, field 2…“? I am using joins with group by clauses and it is causing me MySQL errors due to the “sql_mode=only_full_group_by” - if I can ditch the * and only select specific fields that should solve the problem.

MrBenn

Nothing in the end of lesson GitHub code… https://github.com/silverstripe/silverstripe-lessons-v4/blob/master/Lesson-20-end/app/src/ArticleHolder.php

Show 1 attachment(s)
Lesson-20-end/app/src/ArticleHolder.php

<?php

namespace SilverStripe\Lessons;

use SilverStripe\Forms\GridField\GridField;
use SilverStripe\Forms\GridField\GridFieldConfig_RecordEditor;
use SilverStripe\ORM\ArrayList;
use SilverStripe\Versioned\Versioned;
use SilverStripe\ORM\Queries\SQLSelect;
use SilverStripe\View\ArrayData;
use Page;

class ArticleHolder extends Page
{
    private static $has_many = [
        'Categories' => ArticleCategory::class,
    ];

    private static $allowed_children = [
        ArticlePage::class
    ];

    public function getCMSFields()
    {
        $fields = parent::getCMSFields();
        $fields->addFieldToTab('Root.Categories', GridField::create(
            'Categories',
            'Article categories',
            $this->Categories(),
            GridFieldConfig_RecordEditor::create()
        ));

        return $fields;
    }

    public function Regions ()
    {
        $page = RegionsPage::get()->first();

        if($page) {
            return $page->Regions();
        }
    }

    public function ArchiveDates()
    {
        $list = ArrayList::create();
        $stage = Versioned::get_stage();
        $baseTable = ArticlePage::getSchema()->tableName(ArticlePage::class);
        $tableName = $stage === Versioned::LIVE ? "{$baseTable}_Live" : $baseTable;

        $query = SQLSelect::create()
            ->setSelect([])
            ->selectField("DATE_FORMAT(`Date`,'%Y_%M_%m')", "DateString")
            ->setFrom($tableName)
            ->setOrderBy("DateString", "ASC")
            ->setDistinct(true);

        $result = $query->execute();

        if ($result) {
            foreach ($result as $record) {
                list($year, $monthName, $monthNumber) = explode('_', $record['DateString']);
                $list->push(ArrayData::create([
                    'Year' => $year,
                    'MonthName' => $monthName,
                    'MonthNumber' => $monthNumber,
                    'Link' => $this->Link("date/$year/$monthNumber"),
                    'ArticleCount' => ArticlePage::get()->where([
                        "DATE_FORMAT(\"Date\",'%Y_%m')" => "{$year}_{$monthNumber}",
                        "\"ParentID\"" => $this->ID
                    ])->count()
                ]));
            }
        }

        return $list;
    }
}
Hide attachment content
MrBenn

Hi again, I’m working through the SS4 lessons and I am on https://www.silverstripe.org/learn/lessons/v4/beyond-the-orm-building-custom-sql-1 - I am seeing timeouts when trying to run this code:

  1. public function ArchiveDates()
  2. {
  3. $list = ArrayList::create();
  4. $stage = Versioned::get_stage();
  5. $baseTable = ArticlePage::getSchema()->tableName(ArticlePage::class);
  6. $tableName = $stage === Versioned::LIVE ? "{$baseTable}_Live" : $baseTable;
  7.  
  8. $query = SQLSelect::create()
  9. ->setSelect([])
  10. ->selectField("DATE_FORMAT(`Date`,'%Y_%M_%m')", "DateString")
  11. ->setFrom($tableName)
  12. ->setOrderBy("DateString", "ASC")
  13. ->setDistinct(true);
  14.  
  15. $result = $query->execute();
  16. //up to here it runs fine
  17. if ($result) {
  18. while($record = $result->nextRecord()) {
  19. list($year, $monthName, $monthNumber) = explode('_', $record['DateString']);
  20. //gets stuck in an infinite loop here...
  21.  
  22. $list->push(ArrayData::create([
  23. 'Year' => $year,
  24. 'MonthName' => $monthName,
  25. 'MonthNumber' => $monthNumber,
  26. 'Link' => $this->Link("date/$year/$monthNumber"),
  27. 'ArticleCount' => ArticlePage::get()->where([
  28. "DATE_FORMAT(\"Date\",'%Y_%m')" => "{$year}_{$monthNumber}",
  29. "\"ParentID\"" => $this->ID
  30. ])->count()
  31. ]));
  32. }
  33. }
  34.  
  35. return $list;

I’ve imported the db from the GitHub sql, as it suggests and so have a ~100 ArticlePages to query. Dumping the sql query and results shows the initial query works fine, but once it enters the while($record = $result->nextRecord()) bit, it loops on the first record until it hits the max execution timeout limit. Any ideas what I’ve screwed up here? (SS4.3 on MAMP/ PHP 7.3.1)

Show 1 attachment(s)
silverstripe.org  
SilverStripe Lessons » Beyond the ORM: Building custom SQL

In this tutorial, we'll build a date archive using custom SQL queries, and introduce the SQLQuery class.

Hide attachment content
MrBenn

Arghh! Thanks @Hels (& @mmativ)! Yeah that would explain it!! 🙄