Jump to:

3433 Posts in 1058 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Problem: Querying data from a many_many relation

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

Page: 1
Go to End
Author Topic: 435 Views
  • soliddealer
    Avatar
    Community Member
    5 Posts

    Problem: Querying data from a many_many relation Link to this post

    Hi to everyone here in the forum,

    I have some problems, querying data from a many_many relation.

    What I want to do:

    I have a startpage where I want to filter galleries via keywords.
    Every gallery can have multiple keywords and every keyword can be linked on multiple galleries.
    Now on the startpage I want to create a navigation with the keywords, e.g.:

    Show all
    People
    Animals
    Cars

    in the content area there should be a thumbnail and title from all the galleries related to the keyword. Default state should bring up all galleries without filtering.

    This is my code so far:

    1. Single GalleryPage (FilterGalleryPage.php):

    <?php
    class FilterGalleryPage extends Page {

    public static $many_many = array(
       'Filters' => 'Filter'
    );

    public static $has_many = array(
       'GalleryImages' => 'GalleryImage'
       );
       
       public static $has_one = array(
          'GalleryThumb' => 'Image'
       );

    public function getCMSFields() {
          $fields = parent::getCMSFields();
          
          // Filter
          $gridFieldConfig1 = GridFieldConfig_RelationEditor::create()->addComponents(new GridFieldDeleteAction('unlinkrelation'));
          $gridfield1 = new GridField("Filters", "Filter", $this->Filters(), $gridFieldConfig1);
          $fields->addFieldToTab('Root.Filters', $gridfield1);
                
          // GalleryImage
          $gridFieldConfig2 = GridFieldConfig_RecordEditor::create();
          $gridFieldConfig2->addComponent(new GridFieldBulkEditingTools());
          $gridFieldConfig2->addComponent(new GridFieldBulkImageUpload());
          $gridFieldConfig2->addComponent(new GridFieldSortableRows('SortOrder'));
          $gridfield2 = new GridField("GalleryImages", "Gallery Images", $this->GalleryImages()->sort("SortOrder"), $gridFieldConfig2);
          $fields->addFieldToTab('Root.GalleryImages', $gridfield2);
          
          // GalleryThumb
          $fields->addFieldToTab(
    'Root.Main',
    $uploadField = new UploadField(
    $name = 'GalleryThumb',
    $title = 'Galerie Thumb'
    )
    );
       
          
          
          return $fields;
          
    }

    }

    class FilterGalleryPage_Controller extends Page_Controller {
       
       public static $allowed_actions = array (
       );
       
       public function GetGalleryImages() {
          return $this->GalleryImages()->sort("SortOrder");
       }

       public function init() {
          parent::init();
       }

    }

    2. Filter.php

    <?php

    class Filter extends DataObject {

    public static $db = array(
        'Title' => 'Varchar'
    );

    public static $belongs_many_many = array(
    'FilterGalleryPage' => 'FilterGalleryPage'
    );

    // Summary fields
    public static $summary_fields = array(
    'ID' => 'ID',
        'Title' => 'Title'

    );

    public function getCMSFields() {
          $fields = parent::getCMSFields();
          
          $gridFieldConfig = GridFieldConfig_RelationEditor::create()->addComponents(
             new GridFieldDeleteAction('unlinkrelation')
          );
          $gridFieldConfig->removeComponentsByType('GridFieldAddNewButton');
       
          $gridfield = new GridField("FilterGalleryPage", "FilterGalleryPage", $this->FilterGalleryPage(), $gridFieldConfig);
          $fields->addFieldToTab('Root.FilterGalleryPage', $gridfield);
          return $fields;
          
    }

    }

    3. Startpage (Page.php):

    <?php
    class Page extends SiteTree {

       public static $db = array(
       );

       public static $has_one = array(
       );

    }
    class Page_Controller extends ContentController {

       /**
        * An array of actions that can be accessed via a request. Each array element should be an action name, and the
        * permissions or conditions required to allow the user to access it.
        *
        * <code>
        * array (
        * 'action', // anyone can access this action
        * 'action' => true, // same as above
        * 'action' => 'ADMIN', // you must have ADMIN permissions to access this action
        * 'action' => '->checkAction' // you can only access this action if $this->checkAction() returns true
        * );
        * </code>
        *
        * @var array
        */
       public static $allowed_actions = array (
       );
       
       
       function getFilterwords(){
          $sqlQuery = new SQLQuery();
          $sqlQuery->setFrom('Filter');
          $result = $sqlQuery->execute();
          $returnedRecords = new ArrayList();
          foreach($result as $row) {
             $returnedRecords->push(new ArrayData($row));
          }
          return $returnedRecords;
          }
       function getFiltergals(){
          $curFilterID = 0;
          
          // Show filtered galleries
          if ( isset($_GET['filter']) ) {
             $curFilterID = $_GET['filter'];      
             $sqlQuery = new SQLQuery();
             $sqlQuery->setFrom('filtergallerypage_filters');
             $sqlQuery->addWhere('FilterID = '.$curFilterID);
             $sqlQuery->addLeftJoin('SiteTree', 'SiteTree.ID = filtergallerypage_filters.FilterGalleryPageID');
             $result = $sqlQuery->execute();
             $returnedRecords = new ArrayList();
             foreach($result as $row) {
                $returnedRecords->push(new ArrayData($row));
             }
             return $returnedRecords;
          }
          
          // Show all galleries
          else {
             $returnedRecords = DataObject::get('FilterGalleryPage','', 'Title ASC');
             return $returnedRecords;
          }
          
          
          
          }

       public function init() {
          parent::init();

          // Note: you should use SS template require tags inside your templates
          // instead of putting Requirements calls here. However these are
          // included so that our older themes still work
          Requirements::themedCSS('reset');
          Requirements::themedCSS('layout');
          Requirements::themedCSS('typography');
          Requirements::themedCSS('form');
       }

    }

    My first problem is, that I use two different queries to get the filtered view and the view for all galleries. It would be better for me to use one.
    The second problem is that I cannot retrieve the GalleryThumb Images.

    Can someone help me out. That will save me some more days of errors....

    Thanks in advance and best regards

    Boo

  • zenmonkey
    Avatar
    Community Member
    527 Posts

    Re: Problem: Querying data from a many_many relation Link to this post

    OKay I'm not quite sure why you're doing manual SQL queries instead of just pulling objects and their relations. If you're already setting the filter ID as a URL Parameter, why not just query that Filter object and return all of its FilteredGallleryPages. You're obviously using SS3, so use the new query model

    public function GetFiltergals() {
       $curFilterID = 0;
          
        if ( isset($_GET['filter']) ) {
           $curFilterID = $_GET['filter'];
           return FilterGalleryPage::get()->filter(array("Filter.ID" => $curFilterID));
        } else {
          return FilterGalleryPage::get();
        }

    }

    You only make one call the the Database and you have access the entire object. Then in your tempalte you can jsut do

    <% loop GetFIltergals %>
    <a href="$Link">$GalleryThumb</a>
    <% end_loop %>


    The beauty of this method is that you can then pass multiple filters by just parsing an filter param as an array since the filter "Filter.ID" => $curFilterID can take "Filter.ID" => array() This would act as an 'or' function Filter.ID = Filter1 || Filter2. Or you could loop though the filter array and craete and and filter but doing multiple Filter.ID => calls. Or you could make your parameter string more complex and do things like FIlter1 & Filter2 but not Filter 3
    http://doc.silverstripe.org/framework/en/topics/datamodel

    As an aside, you don't need your GetGalleryImages function. You just <% loop GallertImages %><% end_loop %> in the tempalte when on a FilterGalleryPage or in the GetFIltergals loop.

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