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.


[v5.0.1] Doesn't search catalogue entries

Login / Search

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

Well-settled

Back to the top
 
Posted
Rating:
#63289
Avatar

Hi,

I couldn't actually reproduce this. Do you have a live example?


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:
#63343
Avatar

Well-settled

Hi Chris,

Of course, there is a website available, but unfortunatelly in German only :-)

You are right, it searches the catalogue entries, but doesn't seem to find any results on custom catalogues.

Below is the SQL query which runs agains the DB:

SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id)
LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
JOIN ocp4_translate t0 ON t0.id=a.cv_value JOIN ocp4_translate t1 ON t1.id=b.cv_value
WHERE MATCH (t0.text_original) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1
UNION SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id)
LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
JOIN ocp4_translate t0 ON t0.id=a.cv_value
JOIN ocp4_translate t1 ON t1.id=b.cv_value
WHERE MATCH (t1.text_original) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1
UNION SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id)
LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
JOIN ocp4_translate t0 ON t0.id=a.cv_value JOIN ocp4_translate t1 ON t1.id=b.cv_value
WHERE MATCH (c.cv_value) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1
UNION SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
JOIN ocp4_translate t0 ON t0.id=a.cv_value
JOIN ocp4_translate t1 ON t1.id=b.cv_value WHERE MATCH (d.cv_value) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1
ORDER BY ce_add_date DESC


It doesn't find the entries for the search term "Federgabel". So I put just the first UNION (line 11 to 21) and commented line #9 -> this way all catalogue entries are found as expected:

SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id)
LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
–JOIN ocp4_translate t0 ON t0.id=a.cv_value
JOIN ocp4_translate t1 ON t1.id=b.cv_value
WHERE MATCH (t1.text_original) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1


Don't know why the UNION needs the t0 joined. Anyway, this seems to be the root cause.

Dirk
Back to the top
 
Posted
Rating:
#63345
Avatar

A union just joins stuff together, so just taking the first clause should reduce the same or fewer results, never more.
However one affect of your change is that you cut off the ORDER BY on the end. Could it just be that when ordered by date (in this case) rather than relevance, your desired results aren't at the front?


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:
#63367
Avatar

Well-settled

Sorry if I brought some confusion to you. I mean, I haven't changed the code in ocPortal, I just put the query into my sql tool to narrow down what is causing this behaviour.

Of course, it doesn't make any sense to cut the UNIONS or to disable sort results. But, if you look at this code snippets:

UNION SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id)
LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
—-> JOIN ocp4_translate t0 ON t0.id=a.cv_value  ←–
JOIN ocp4_translate t1 ON t1.id=b.cv_value
WHERE MATCH (t1.text_original) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1

as well as

UNION SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value
FROM ((ocp4_catalogue_fields f,ocp4_catalogue_entries r)
LEFT JOIN ocp4_catalogue_efv_short_trans a ON (r.id=a.ce_id AND f.id=a.cf_id)
LEFT JOIN ocp4_catalogue_efv_long_trans b ON (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocp4_catalogue_efv_short c ON (r.id=c.ce_id AND f.id=c.cf_id)
LEFT JOIN ocp4_catalogue_efv_long d ON (r.id=d.ce_id AND f.id=d.cf_id)
LEFT JOIN ocp4_group_category_access z ON (z.module_the_name='catalogues_category' AND z.category_name=r.cc_id AND z.group_id=1)
LEFT JOIN ocp4_group_category_access p ON (p.module_the_name='catalogues_catalogue' AND p.category_name=r.c_name AND p.group_id=1))
—-> JOIN ocp4_translate t0 ON t0.id=a.cv_value ←–
JOIN ocp4_translate t1 ON t1.id=b.cv_value WHERE MATCH (d.cv_value) AGAINST ('Federgabel') AND z.category_name IS NOT NULL AND p.category_name IS NOT NULL AND ce_validated=1


it joins ocp4_translate as t0 AND a second join as t1, but t0 is nowhere being used in the specific UNION whereas the t1 is being used for the MATCH…AGAINST (search criteria).

What I wanted to show is, if you remove the JOIN as t0, the sql query works fine. And, to be honest, I can't seem to find why the JOIN t0 is required.

Hope I was able to point out what I mean, and sorry for my English..  ;)

Thanks
Back to the top
 
Posted
Rating:
#63369
Avatar

Wow, that's bizarre, must be a MySQL bug. That would explain why I couldn't reproduce as in 5.0.2 we have removed the unneeded joins.


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:
#63401
Avatar

Well-settled

Hi Chris,

I just upgraded from 5.0.1 to 5.0.2 beta and confirm that all catalogue entries are searched and found as expected.

Btw, I feel if 5.0.2 speeds up the website dramatically. It's really amazing how fast the sites show up.

Dirk
Back to the top
 
Posted
Rating:
#63402
Avatar

There were a couple of fairly decent optimisations in there and lots of little ones :). Glad it shows, I'm never sure how well these changes work in practice.


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:
#63404
Avatar

Community saint

Ragtop said

Btw, I feel if 5.0.2 speeds up the website dramatically. It's really amazing how fast the sites show up.

Dirk

Chris Graham said

There were a couple of fairly decent optimisations in there and lots of little ones :).

That's good enough for me! Off to get the beta …

Take my advice. I'm not using it!

View my working ocPortal site (version 9.x.x) at Anglo-Indian Portal
Back to the top
 
There are too many online users to list.
Control functions:

Quick reply   Contract

Your name:
Your message: