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 /

Search Results Pagination Problems


Go to End
Reply


10 Posts   4690 Views

Avatar
Hamish

Community Member, 712 Posts

14 August 2009 at 8:36am

Horay! Yeah, I used used buildDataObjectSet because I was selected a particular object (ie, didn't need custom select fields) but had a very complex filter and sorting query.

Avatar
Garrett

Community Member, 245 Posts

19 November 2010 at 8:56am

Hi Hamish, need to touch base on this issue again....

I have just upgraded this site to 2.4.3 and this custom search query function and pagination has been broken. The problem SEEMS to lie in parseQueryLimit() in DataObjectSet.php. I am hoping you or someone will be willing to really look at this code to help me with my problem. What is happening is that although there are no changes to the code, WITH the call to parseQueryLimit() I am getting an error, and if I comment out the call to parseQueryLimit(), the page loads but each pages advances only 10 items when I have 24 specified in the code so it's a mess. Here is my function:

   public function NewsroomSearchResults($limit = 24) {

      $start = isset($_GET['start']) ? (int)$_GET['start'] : 0;

      $results = new DataObjectSet();

      $criterion = (Convert::raw2SQL($_GET['q']) != "Newsroom Search") ? Convert::raw2SQL($_GET['q']) : "";      
      $cat = (isset($_GET['cat'])) ? Convert::raw2SQL($_GET['cat']) : "";      
      $category = (isset($_GET['category'])) ? Convert::raw2SQL($_GET['category']) : "";      
      $category = ($category!=null && $category!="") ? $category : $cat;
      $category_check = ($category != "Categories") ? "AND (BE.Category LIKE '%".$category."%' OR GP.Category LIKE '%".$category."%')" : "";
      $tag_check = "";

      if(isset($_GET['tag'])) {
         $tag = addslashes($_GET['tag']);
         $tag = str_replace(array("\\",'_','%',"'"), array("\\\\","\\_","\\%","\\'"), $tag);
         $tag_check = "AND (BE.Tags LIKE '%$tag%' OR GP.Tags LIKE '%$tag%' OR GPIA.Tags LIKE '%$tag%')";
      }

      $is_search = ($criterion!=null && $criterion!="") ? true : false;
      $crit_or_tag_str = ($is_search) ? "AND (ST.Content LIKE '%".$criterion."%' OR ST.Title LIKE '%".$criterion."%' OR BE.Tags LIKE '%".$criterion."%' OR GP.Tags LIKE '%".$criterion."%' OR GP.Author LIKE '%".$criterion."%' OR GPIA.Tags LIKE '%".$criterion."%')" : $tag_check;

      $query = new SQLQuery();
      $query->select = array("ST.Title, ST.Content, ST.URLSegment, ST.Created, ST.Status, BE.*, COUNT(PC.ID) AS NumComments, GP.*, GPIA.*, F.FileName AS ListingImage");
      $query->from = array("
         SiteTree ST
         LEFT JOIN BlogEntry BE ON BE.ID = ST.ID
         LEFT JOIN GalleryPage GP ON GP.ID = ST.ID
         LEFT JOIN ( SELECT * FROM GalleryImageAttachment GPIA ORDER BY GPIA.ID ASC )
            GPIA ON GP.ID = GPIA.GalleryPageID
      LEFT JOIN Clients C ON C.ClientName = BE.Client
         LEFT JOIN PageComment PC ON PC.ParentID = ST.ID AND PC.NeedsModeration = 0 AND PC.IsSpam = 0
         LEFT JOIN File F ON (F.ID=BE.ListingImageID OR F.ID=GPIA.ImageID OR F.ID=C.ClientLogoID)
         ");
      $query->where = array("ST.Status = 'Published' $category_check $crit_or_tag_str");
      $query->groupby = array("ST.ID");
      $query->orderby = "BE.Date DESC, GP.Date DESC";
      $query->limit = "$start, $limit";

      //die($query);
      
      $result = $query->execute();
      
      foreach($result as $sqlResult) {
      
         $Title = $sqlResult['Title'];
         $Content = $sqlResult['Content'];
         $Description = $sqlResult['Description'];
         $ListingImage = $sqlResult['ListingImage'];
         $VideoID = $sqlResult['VideoID'];
         $URLSegment = $sqlResult['URLSegment'];
         $Date        = $sqlResult['Date'];
         $CreatedDate = $sqlResult['Created'];
         $Author = $sqlResult['Author'];
         $ExternalAuthor = $sqlResult['ExternalAuthor'];
         $Tags = $sqlResult['Tags'];
         $Category = $sqlResult['Category'];
         $Source = $sqlResult['Source'];
         $URL     = $sqlResult['URL'];
         $Client = $sqlResult['Client'];
         $NumComments = $sqlResult['NumComments'];
      
         $HasAuthor = ($Author != "Please Select" && $Author != "") ? true : false;
         $HasExternalAuthor = ($ExternalAuthor != "") ? true : false;
         $HasSource = ($Source != "") ? true : false;
         $Thumbnail = ($VideoID!=null && $VideoID!="") ? $this->GetVimeoThumb($VideoID) : "";
         
         if($Tags!="") {
            $theseTags = split(" *, *", $Tags);
            $tagoutput = new DataObjectSet();
            foreach($theseTags as $tag) {
               $tagoutput->push(new ArrayData(array(
                  "Tag" => $tag,
                  "SearchURL" => $this->URLSegment."?q=&tag=$tag"
               )));
            }
         } else {
            $tagoutput = "";
         }

         $theseCats = split(" *, *", $Category);
         $catoutput = new DataObjectSet();
         foreach($theseCats as $cat) {
            $catoutput->push(new ArrayData(array(
               "Cat" => $cat,
               "SearchURL" => $this->URLSegment."?q=&cat=$cat",
               "conCat" => str_replace(" ","",ucwords($cat))
            )));
         }

         $results->push(new ArrayData(array(
            "Title"    => $this->trunc(strip_tags($Title), 9),
            "Content"    => $this->trunc(strip_tags($Content), 25),
            "Description" => $Description,
            "ListingImage" => $ListingImage,
            "Thumbnail" => $Thumbnail,
            "URLSegment" => $URLSegment,
            "Date" => date('F d, Y', strtotime($Date)),
            "Author" => $Author,
            "ExternalAuthor" => $ExternalAuthor,
            "Tags" => $tagoutput,
            "Category"    => $catoutput,
            "Source"       => $Source,
            "URL"          => $URL,
            "Client"       => $Client,
            "NumComments" => $NumComments,
            "HasAuthor" => $HasAuthor,
            "HasSource" => $HasSource,
            "HasExternalAuthor" => $HasExternalAuthor,
            "StrippedBaseHref" => substr_replace(Director::absoluteBaseURL() ,"",-1)
         )));
         
      }
      
      //$results->parseQueryLimit($query);
      
      return $results;
   }

I know that's a lot to read, but the important part is this -- if I do a die($query), and take that output and paste it into a query in the database, it's perfect:

SELECT ST.Title, ST.Content, ST.URLSegment, ST.Created, ST.Status, BE.*, COUNT(PC.ID) AS NumComments, GP.*, GPIA.*, F.FileName AS ListingImage
FROM SiteTree ST LEFT JOIN BlogEntry BE ON BE.ID = ST.ID
LEFT JOIN GalleryPage GP ON GP.ID = ST.ID
LEFT JOIN ( SELECT * FROM GalleryImageAttachment GPIA ORDER BY GPIA.ID ASC )GPIA ON GP.ID = GPIA.GalleryPageID
LEFT JOIN Clients C ON C.ClientName = BE.Client
LEFT JOIN PageComment PC ON PC.ParentID = ST.ID AND PC.NeedsModeration = 0 AND PC.IsSpam = 0
LEFT JOIN File F ON (F.ID=BE.ListingImageID OR F.ID=GPIA.ImageID OR F.ID=C.ClientLogoID)
WHERE (ST.Status = 'Published' AND (BE.Category LIKE '%Clients in the News%' OR GP.Category LIKE '%Clients in the News%'))
GROUP BY ST.ID ORDER BY BE.Date DESC, GP.Date DESC LIMIT 0, 24

I get the first 24 of 265 results. But when I call parseQueryLimit(), I get the following error:

[User Error] Couldn't run query: SELECT count(*) FROM SELECT ST.Title, ST.Content, ST.URLSegment, ST.Created, ST.Status, BE.*, COUNT(PC.ID) AS NumComments, GP.*, GPIA.*, F.FileName AS ListingImage FROM SiteTree ST LEFT JOIN BlogEntry BE ON BE.ID = ST.ID LEFT JOIN GalleryPage GP ON GP.ID = ST.ID LEFT JOIN ( SELECT * FROM GalleryImageAttachment GPIA ORDER BY GPIA.ID ASC ) GPIA ON GP.ID = GPIA.GalleryPageID LEFT JOIN Clients C ON C.ClientName = BE.Client LEFT JOIN PageComment PC ON PC.ParentID = ST.ID AND PC.NeedsModeration = 0 AND PC.IsSpam = 0 LEFT JOIN File F ON (F.ID=BE.ListingImageID OR F.ID=GPIA.ImageID OR F.ID=C.ClientLogoID) WHERE (ST.Status = 'Published' AND (BE.Category LIKE '%Clients in the News%' OR GP.Category LIKE '%Clients in the News%') ) GROUP BY ST.ID) all_distinct Duplicate column name 'ID'

Where is this all_distinct thing coming from? What duplicate column name? And this doesn't even look like my query. What's the SELECT count(*) at the beginning for example?

Any help you could offer me would be greatly appreciated. I just can't figure out why this is broken. I even tried the old version or parseQueryLimit() and that did nothing.

Thanks,
Garrett

Go to Top