HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


[CLOSED] Phantom search results with "Catalogue entries"

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#68553 (In Topic #14562)
Avatar

Community saint

When I do an advanced search and check "Catalogue entries", the search always return all catalogue entries (in my case 3 dummy faq questions), regardless of what I am actually searching for.


Last edit: by temp1024

Do you have a Samsung Galaxy S / Galaxy S II ? If so, why not check out my ScreenFree FM Radio .
Back to the top
 
Posted
Rating:
#68604
Avatar

I couldn't reproduce. I tried various things with a test entry in the default FAQs catalogue and it all worked. If this is not the default FAQ catalogue please mention the field types you have (long text, etc).


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#68664
Avatar

Community saint

I'm using the default FAQ structure. I just have 3 nonsense entries, like "FAQ Question #1":
sadil asld asdas
das
 d
asd
as
 d
as
d
Just had another play. If I search for "rrr" it finds nothing, but if I search for "rrra", it finds the above entry.

In both cases "Find exact matches" is checked.

At first I though it must have just been fuzzy matching kicking in, but if it was then it still should not have returned these results.

Do you have a Samsung Galaxy S / Galaxy S II ? If so, why not check out my ScreenFree FM Radio .
Back to the top
 
Posted
Rating:
#68666
Avatar

MySQL fulltext can be pretty weird when it can't find obvious matches for junk terms, maybe that's it.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#68668
Avatar

Community saint

Maybe, but it's strange that its doing this just for the catalogue entries. I have the same type of nonsense in blogs and forum posts and its not picking them up.

Do you have a Samsung Galaxy S / Galaxy S II ? If so, why not check out my ScreenFree FM Radio .
Back to the top
 
Posted
Rating:
#68669
Avatar

If you want to inspect the query that is running you can add &keep_show_query=1 to the URL.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#68674
Avatar

Community saint

Bad - Searching for a 4 letter word "yyyy":

Code

SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,MATCH (t0.text_original) AGAINST ('"yyyy"' IN BOOLEAN MODE) AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) JOIN ocpo_translate t0 ON t0.id=a.cv_value AND t0.language='EN' WHERE MATCH (t0.text_original) AGAINST ('"yyyy"' IN BOOLEAN MODE) UNION SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,MATCH (t1.text_original) AGAINST ('"yyyy"' IN BOOLEAN MODE) AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) JOIN ocpo_translate t1 ON t1.id=b.cv_value AND t1.language='EN' WHERE MATCH (t1.text_original) AGAINST ('"yyyy"' IN BOOLEAN MODE) UNION SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,MATCH (c.cv_value) AGAINST ('"yyyy"' IN BOOLEAN MODE) AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) WHERE MATCH (c.cv_value) AGAINST ('"yyyy"' IN BOOLEAN MODE) UNION SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,MATCH (d.cv_value) AGAINST ('"yyyy"' IN BOOLEAN MODE) AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) WHERE MATCH (d.cv_value) AGAINST ('"yyyy"' IN BOOLEAN MODE) GROUP BY r.id ORDER BY contextual_relevance DESC
Good - Searching for 3 letter word "yyy"

Code

SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,(t0.text_original LIKE '%yyy%') AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) JOIN ocpo_translate t0 ON t0.id=a.cv_value AND t0.language='EN' WHERE (t0.text_original LIKE '%yyy%') UNION SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,(t1.text_original LIKE '%yyy%') AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) JOIN ocpo_translate t1 ON t1.id=b.cv_value AND t1.language='EN' WHERE (t1.text_original LIKE '%yyy%') UNION SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,(c.cv_value LIKE '%yyy%') AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) WHERE (c.cv_value LIKE '%yyy%') UNION SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value,(d.cv_value LIKE '%yyy%') AS contextual_relevance FROM ocpo_catalogue_fields f LEFT JOIN ocpo_catalogue_entries r ON (r.c_name=f.c_name) LEFT JOIN ocpo_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocpo_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocpo_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id) LEFT JOIN ocpo_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id) WHERE (d.cv_value LIKE '%yyy%') GROUP BY r.id ORDER BY contextual_relevance DESC
The only difference between a good search and a bad search is supposed to be the search term, yet the SQL structure is different (Dropping MATCH, LIKE vs AGAINST, etc).

Do you have a Samsung Galaxy S / Galaxy S II ? If so, why not check out my ScreenFree FM Radio .
Back to the top
 
Posted
Rating:
#68802
Avatar

Yeah, that pretty much confirms it as a MySQL fulltext search inprecision issue. I checked the query and it looks properly constructed.

The reason for the disparity between your two queries is that ocPortal switches to using 'LIKE' when it gets beneath the word length limit for MySQL fulltext search.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#68831
Avatar

Community saint

OK, I'll buy that its a SQL fulltext issue, but why does it only seem to affect Catalogue entries?

Do you have a Samsung Galaxy S / Galaxy S II ? If so, why not check out my ScreenFree FM Radio .
Back to the top
 
Posted
Rating:
#68836
Avatar

Don't really know I'm afraid. All I know is MySQL does some weird statistical stuff that leads to odd results when there is a lack of data. Probably there is a sour spot when there is not much data for a keyword, but not nothing at all for the data type.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#68844
Avatar

Community saint

That explanation works for me.

Do you have a Samsung Galaxy S / Galaxy S II ? If so, why not check out my ScreenFree FM Radio .
Back to the top
 
There are too many online users to list.
Control functions:

Quick reply   Contract

Your name:
Your message: