Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Problem: Querying data from a many_many relation


Reply


2 Posts   522 Views

Avatar
soliddealer

Community Member, 5 Posts

26 June 2013 at 4:43am

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

Avatar
zenmonkey

Community Member, 532 Posts

13 July 2013 at 5:02pm

Edited: 13/07/2013 5:05pm

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.