Forum
Runway search pages and blog
I have a runway site in development with the blog app (Runway v2.8.32, Blog 5.0)
When searching for a term that is only present in blog posts search works perfectly. However, if I add that same word to a region then search only returns the page results and not the blog entries.
With the term "sagittis" only present in 2 blog posts the Debug output is
[24] SELECT DISTINCT settingID, settingValue FROM tdnr_settings WHERE userID=0
[nil] SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, MATCH(ci.itemSearch) AGAINST('sagittis') AS score, r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8 FROM tdnr_content_regions r, tdnr_content_items ci, tdnr_pages p WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 AND (MATCH(ci.itemSearch) AGAINST('sagittis') OR MATCH(ci.itemSearch) AGAINST('sagittis') ) AND r.regionPage LIKE '/%' UNION SELECT 'PerchContent_RunwaySearch' AS source, MATCH(ci.itemSearch) AGAINST('sagittis') AS score, c.collectionKey AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, c.collectionOptions AS col4, c.collectionKey AS col5, c.collectionKey AS col6, collectionTemplate AS col7, c.collectionKey AS col8 FROM tdnr_collections c, tdnr_collection_items ci, tdnr_collection_revisions cr WHERE c.collectionID=cr.collectionID AND cr.itemID=ci.itemID AND cr.itemRev=ci.itemRev AND c.collectionSearchable=1 AND cr.itemSearchable=1 AND (MATCH(ci.itemSearch) AGAINST('sagittis') OR MATCH(ci.itemSearch) AGAINST('sagittis') ) UNION SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('sagittis') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM tdnr_blog_posts p, tdnr_blog_sections s WHERE postStatus='Published' AND postDateTime<'2016-11-14 18:31:41' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('sagittis') ORDER BY score DESC LIMIT 0, 1000
[2] SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, 0-(LENGTH(r.regionPage)-LENGTH(REPLACE(r.regionPage, '/', ''))) AS score, r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8 FROM tdnr_content_regions r, tdnr_content_items ci, tdnr_pages p WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 AND ci.itemSearch REGEXP '[[:<:]]sagittis[[:>:]]' AND r.regionPage LIKE '/%' UNION SELECT 'PerchContent_RunwaySearch' AS source, 1 AS score, c.collectionKey AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, c.collectionOptions AS col4, c.collectionKey AS col5, c.collectionKey AS col6, collectionTemplate AS col7, c.collectionKey AS col8 FROM tdnr_collections c, tdnr_collection_items ci, tdnr_collection_revisions cr WHERE c.collectionID=cr.collectionID AND cr.itemID=ci.itemID AND cr.itemRev=ci.itemRev AND c.collectionSearchable=1 AND cr.itemSearchable=1 AND ci.itemSearch REGEXP '[[:<:]]sagittis[[:>:]]' UNION SELECT 'PerchBlog_SearchHandler' AS source, postDateTime AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM tdnr_blog_posts p, tdnr_blog_sections s WHERE postStatus='Published' AND postDateTime<'2016-11-14 18:31:41' AND p.sectionID=s.sectionID AND ( concat(" ", postTitle, " ") REGEXP '[[:<:]]sagittis[[:>:]]' OR concat(" ", postDescRaw, " ") REGEXP '[[:<:]]sagittis[[:>:]]' OR concat(" ", postTags, " ") REGEXP '[[:<:]]sagittis[[:>:]]' ) ORDER BY score ASC LIMIT 0, 1000
[2] SELECT FOUND_ROWS() AS `count`
[2] Using template: /templates/search/search-result.html
Request time: 0.1428
Process time: 0.0997
Memory: 2.3795
But if I then add it to a region (without changing the blog posts) search only returns the page result.
[36] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM tdnr_pages p LEFT JOIN tdnr_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC
Matched page: /search-results, so not using routes.
Using master page: /templates/pages/search.php
[1] SELECT * FROM tdnr_pages WHERE pagePath='/search-results' LIMIT 1
Search term: sagittis
[24] SELECT DISTINCT settingID, settingValue FROM tdnr_settings WHERE userID=0
[1] SELECT SQL_CALC_FOUND_ROWS DISTINCT 'PerchContent_SearchHandler' AS source, MATCH(ci.itemSearch) AGAINST('sagittis') AS score, r.regionPage AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, r.regionOptions AS col4, p.pageNavText AS col5, p.pageTitle AS col6, regionTemplate AS col7, r.regionKey AS col8 FROM tdnr_content_regions r, tdnr_content_items ci, tdnr_pages p WHERE r.regionID=ci.regionID AND r.regionRev=ci.itemRev AND r.pageID=p.pageID AND r.regionPage!='*' AND r.regionSearchable=1 AND (MATCH(ci.itemSearch) AGAINST('sagittis') OR MATCH(ci.itemSearch) AGAINST('sagittis') ) AND r.regionPage LIKE '/%' UNION SELECT 'PerchContent_RunwaySearch' AS source, MATCH(ci.itemSearch) AGAINST('sagittis') AS score, c.collectionKey AS col1, ci.itemSearch AS col2, ci.itemJSON AS col3, c.collectionOptions AS col4, c.collectionKey AS col5, c.collectionKey AS col6, collectionTemplate AS col7, c.collectionKey AS col8 FROM tdnr_collections c, tdnr_collection_items ci, tdnr_collection_revisions cr WHERE c.collectionID=cr.collectionID AND cr.itemID=ci.itemID AND cr.itemRev=ci.itemRev AND c.collectionSearchable=1 AND cr.itemSearchable=1 AND (MATCH(ci.itemSearch) AGAINST('sagittis') OR MATCH(ci.itemSearch) AGAINST('sagittis') ) UNION SELECT 'PerchBlog_SearchHandler' AS source, MATCH(postTitle, postDescRaw, postTags) AGAINST('sagittis') AS score, postTitle, postSlug, postDateTime, postDescHTML, postID, sectionSlug, "", "" FROM tdnr_blog_posts p, tdnr_blog_sections s WHERE postStatus='Published' AND postDateTime<'2016-11-14 18:36:35' AND p.sectionID=s.sectionID AND MATCH(postTitle, postDescRaw, postTags) AGAINST('sagittis') ORDER BY score DESC LIMIT 0, 1000
[1] SELECT FOUND_ROWS() AS `count`
[1] Using template: /templates/search/search-result.html
Request time: 0.0703
Process time: 0.0686
Memory: 2.3706
The Diagnostic summary is:
Summary information
• Perch Runway: 2.8.32, PHP: 5.5.35, MySQL: 5.6.30, with PDO
• Server OS: Darwin, apache2handler
• Installed apps: content (2.8.32), assets (2.8.32), categories (2.8.32), perch_blog (5.0), perch_forms (1.8.3), perch_members (1.5)
• App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_forms', 'perch_blog', 'perch_members', );
• PERCH_LOGINPATH: /sqc-admin
• PERCH_PATH: /Volumes/MacHDD/WebDevelopment/SQC2016/sqc-admin
• PERCH_CORE: /Volumes/MacHDD/WebDevelopment/SQC2016/sqc-admin/core
• PERCH_RESFILEPATH: /Volumes/MacHDD/WebDevelopment/SQC2016/sqc-admin/resources
• Image manipulation: GD
• PHP limits: Max upload 2M, Max POST 8M, Memory: 128M, Total max file upload: 2M
• F1: 2edba60ed1f613d6dd804feb202456a2
• Resource folder writeable: Yes
• HTTP_HOST: sqc.dev
• DOCUMENT_ROOT: /Volumes/MacHDD/WebDevelopment/SQC2016
• REQUEST_URI: /sqc-admin/core/settings/diagnostics/
SCRIPT_NAME: /sqc-admin/core/settings/diagnostics/index.php
The page code is:
<?php
$query = perch_get('q');
perch_content_search($query, array(
'template'=>'search-result.html',
'count'=>1000
));
?>
The search form template:
<perch:form id="search" class="modal" method="get" action="/search-results">
<perch:input class="search" placeholder="Search" type="search" id="q" />
<perch:input id="go" class="go" type="submit" value="Go" />
</perch:form>
and the results template:
<perch:before>
<div id="search-results">
<h1>Search results for “<perch:search id="search_key" />”</h1>
<perch:if exists="paging">
<div class="paging">
<perch:if exists="not_first_page">
<a class="paging-link" href="<perch:search id="prev_url" encode="false" />">Previous</a>
</perch:if>
Page <perch:search id="current_page" /> of <perch:search id="number_of_pages" />
<perch:if exists="not_last_page">
<a class="paging-link" href="<perch:search id="next_url" encode="false" />">Next</a>
</perch:if>
<p><perch:search id="total" /> results</p>
</div>
</perch:if>
<ul>
</perch:before>
<li class="<perch:search id="perch_item_odd" />">
<h2><a href="<perch:search id="result_url" />"><perch:search id="result_title" /></a></h2>
<perch:if exists="result_excerpt"><p class="excerpt">…<perch:search id="result_excerpt" encode="false" />…</p></perch:if>
<p><a href="<perch:search id="result_url" />">
<perch:if exists="result_pageNavText">
<perch:search id="result_pageNavText" />
<perch:else />
<perch:search id="result_url" />
</perch:if>
</a>
<perch:search id="result_source" />
</p>
</li>
<perch:after>
</ul>
<perch:if exists="paging">
<div class="paging">
<perch:if exists="not_first_page">
<a class="paging-link" href="<perch:search id="prev_url" encode="false" />">Previous</a>
</perch:if>
Page <perch:search id="current_page" /> of <perch:search id="number_of_pages" />
<perch:if exists="not_last_page">
<a class="paging-link" href="<perch:search id="next_url" encode="false" />">Next</a>
</perch:if>
</div>
</perch:if>
</div>
</perch:after>
<perch:noresults>
<div id="search-results">
<perch:if exists="search_key">
<p class="no-results">Sorry, there are no results for “<perch:search id="search_key" />”.</p>
<perch:else />
<p class="no-results">please eneter a search term</p>
</perch:if>
</div>
</perch:noresults>
I've tried it on a couple of different server configurations but always with the same results.
The first time, the full text search is failing to match (probably either due to insufficient data, or too frequent occurrence of the search string within the available data). What happens then is we drop back to a very crude 'manual' search with a basic SQL query rather than using the index. Those are the results you're seeing.
When you add the keyword to the collection, there's more data to work with and the full text search returns the result from the collection. I suspect the term still appears too frequently in blog so those results are omitted.
That's why the different results - two different search methods.
As always, unless you already have your production level of data in the system and are searching for known common queries, it's probably not worth over analysing any specific search result.
Drew.
Thanks for the reply, I've read up on the API interface for search handlers and understand the backup sql. I've created some production data and the bit that still puzzles me is if I restrict the perch_content_search to a single App I get exactly the results that I expect.
Searching Content
Searching Bog
But if I search for both (either with an apps array or by leaving out the apps option) I only get the Content results. It would seem that the union of the results is where I am experiencing issues.
As search accuracy is important for this application I'm keen to make sure I have some options. If when I test this on full production data the results are not acceptable I think the solutions would be to:
Any thoughts on what would be the best approach? Are there any constraints on the SQL that can be provided by a search handler?
If search is an important aspect, you should also consider
3 - Using a dedicated search server like Solr or ElasticSearch
What we have is currently is (2) in your list.