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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

Search Results Pagination Problems


Go to End


10 Posts   6286 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