Jump to:

3430 Posts in 1057 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Search Results Pagination Problems

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

Page: 1 2
Go to End
Author Topic: 4445 Views
  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Search Results Pagination Problems Link to this post

    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.

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: Search Results Pagination Problems Link to this post

    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

    4445 Views
Page: 1 2
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.