Joomla search on a list having DBj element rendered as checkbox crash

[lcollong]

Hi,

The Joomla search is set on a list named "compagnies" which contains a dbj element to a foreign table as multi-select checkbox "type of compagnies". The search setup is on several columns including this one.

It crashes with the following ( see the last part WHERE with an unclosed quoted separator) .

It can be tested on the "vif" site of mySites. Just type some search in the top right joomla search field.

It should be due to some recent change in Joomla or Fabrik because a previous "clone" of this site running J3.6.5 /F3.5.1 has the same setup without generating this error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//) AGAINST ('test*' IN BOOL' at line 44 SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea`,
`f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea_raw`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id_raw`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie_raw`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame_raw`,
`f_compagnies_maritimes`.`fk_pays` AS `f_compagnies_maritimes___fk_pays_raw`,
`f_pays`.`nom_fr_FR` AS `f_compagnies_maritimes___fk_pays`,
`f_compagnies_maritimes`.`fk_ville` AS `f_compagnies_maritimes___fk_ville_raw`,
`f_villes`.`nom_ville` AS `f_compagnies_maritimes___fk_ville`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet_raw`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo_raw`,
(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_raw`, (SELECT GROUP_CONCAT(type_compagnie SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie_id,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie___params`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone_raw`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis_raw`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut_raw`,
`f_compagnies_maritimes`.`parent` AS `f_compagnies_maritimes___parent_raw`,
`f_compagnies_maritimes_0`.`nom_compagnie` AS `f_compagnies_maritimes___parent`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe_raw`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps_raw`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address_raw`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address_raw`,
`f_compagnies_maritimes`.`id` AS slug
, `f_compagnies_maritimes`.`id` AS `__pk_val`

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE (  (MATCH(`f_compagnies_maritimes`.`nom_compagnie`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( `f_pays`.`nom_fr_FR`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( `f_villes`.`nom_ville`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( `f_compagnies_maritimes`.`site_internet`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( (SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//) AGAINST ('test*' IN BOOLEAN MODE)) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC LIMIT 0, 100

 

[cheesegrits]

OK, I think I see the problem. And I suspect it didn't really work before, it just didn't actually throw an error.

As per this comment on the change I made in that code in 3.6 ...

                /*
                * Meh.  This next bit is a nasty hack.  Turns out you can't mix different tables in a
                * MATCH(...) AGAINST (...), so we have to split it out into one MATCH per table, joined with OR.
                * This should really be done when building the search in doBooleanSearch() in the list filter model,
                * but the concept of a single filter with index 9999 is fairly baked-in, so creating multiple filters
                * for a single searchall will be problematic.  So lets hack it here, by seeing if the MATCH(..) list
                * uses more than one table, and if so massage it into multiple MATCH.
                */

So in the past, we would have done "MATCH(table1.foo,table2,foo,table3.foo) AGAINST ('test*' IN BOOLEAN MODE)" which wouldn't have actually worked, although I think it would have just silently failed and not searched table2 and table3. So now we split that up into "MATCH(table1.foo) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH(table2.foo) AGAINST ('test*' IN BOOLEAN MODE) ...".

Except I think my code moofs up when you have a multiselect, because this line:

$parts = explode('.', $field);

... which is supposed to figure out the table name of the MATCH(table1.foo) wont work for "MATCH((SELECT GROUP_CONCAT(lookup.nom_type_co SEPARATOR'//..*../')" as it matches the . in the seperator.

Working on it ...

-- hugh

 

[lcollong]

You would have made an excellent doctor !
Diagnosis is so much clear and explanations to the "patient" are promising (r?confortantes in French) !
Thanks for looking at this one.

 

[cheesegrits]

Hmm, are you sure that ever actually worked?

In a MySQL fulltext search, the arguments to the MATCH (...) AGAINST must be fields. They can't be subqueries. So I don't see how doing the fulltext searching against a repeated join (which uses the funky "SELECT GROUP_CONCAT ..." subquery) could ever have worked.

Can you try it on your old system, and append &fabrikdebug=1 to the search page URL. So after your search, you should have a url like ...

search.html?searchword=test&ordering=newest&searchphrase=any&limit=20

Add &fabrikdebug=1 to that. You should then get a standard Fabrik debug output, with a "list GetData" row. You may have to view the source of the page to see it - for some reason the debug output won't expand on click on the J! search results page (I think some JS is missing).

Find the WHERE part of the main getData query, and paste it here.

-- hugh

 

[lcollong]

I've changed the setup to have something comparable.
Here is the error similar to the previous one :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//) AGAINST ('test*' IN BOOL' at line 44 SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea`,
`f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea_raw`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id_raw`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie_raw`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame_raw`,
`f_compagnies_maritimes`.`fk_pays` AS `f_compagnies_maritimes___fk_pays_raw`,
`f_pays`.`nom_fr_FR` AS `f_compagnies_maritimes___fk_pays`,
`f_compagnies_maritimes`.`fk_ville` AS `f_compagnies_maritimes___fk_ville_raw`,
`f_villes`.`nom_ville` AS `f_compagnies_maritimes___fk_ville`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet_raw`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo_raw`,
(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_raw`, (SELECT GROUP_CONCAT(type_compagnie SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie_id,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie___params`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone_raw`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis_raw`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut_raw`,
`f_compagnies_maritimes`.`parent` AS `f_compagnies_maritimes___parent_raw`,
`f_compagnies_maritimes_0`.`nom_compagnie` AS `f_compagnies_maritimes___parent`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe_raw`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps_raw`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address_raw`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address_raw`,
`f_compagnies_maritimes`.`id` AS slug
, `f_compagnies_maritimes`.`id` AS `__pk_val`

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE (  (MATCH(`f_compagnies_maritimes`.`nom_compagnie`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( `f_pays`.`nom_fr_FR`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( `f_villes`.`nom_ville`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( `f_compagnies_maritimes`.`site_internet`) AGAINST ('test*' IN BOOLEAN MODE) OR MATCH( (SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//) AGAINST ('test*' IN BOOLEAN MODE)) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC LIMIT 0, 100

And here is the debug result on the site which works without errors :

SELECT SQL_CALC_FOUND_ROWS DISTINCT `f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea`,
`f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea_raw`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id_raw`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie_raw`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame_raw`,
`f_compagnies_maritimes`.`fk_pays` AS `f_compagnies_maritimes___fk_pays_raw`,
`f_pays`.`nom_fr_FR` AS `f_compagnies_maritimes___fk_pays`,
`f_compagnies_maritimes`.`fk_ville` AS `f_compagnies_maritimes___fk_ville_raw`,
`f_villes`.`nom_ville` AS `f_compagnies_maritimes___fk_ville`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet_raw`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo_raw`,
(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_raw`, (SELECT GROUP_CONCAT(type_compagnie SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie_id,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie___params`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone_raw`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis_raw`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut_raw`,
`f_compagnies_maritimes`.`parent` AS `f_compagnies_maritimes___parent_raw`,
`f_compagnies_maritimes_0`.`nom_compagnie` AS `f_compagnies_maritimes___parent`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe_raw`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps_raw`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address_raw`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address_raw`,
CONCAT_WS(':', `f_compagnies_maritimes`.`id`, `f_compagnies_maritimes`.`nom_compagnie`) AS slug
, `f_compagnies_maritimes`.`id` AS `__pk_val`

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE ( (  LOWER(`f_compagnies_maritimes`.`nom_compagnie`) regexp LOWER('test')  OR  LOWER(`f_pays`.`nom_fr_FR`) regexp LOWER('test') OR  LOWER(`f_villes`.`nom_ville`) regexp LOWER('test') OR   LOWER(`f_compagnies_maritimes`.`site_internet`) regexp LOWER('test')  OR  LOWER((SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`)) regexp LOWER('test') OR   LOWER(`f_compagnies_maritimes`.`compagnie_groupe`) regexp LOWER('test') ) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC

 

[cheesegrits]

Hmmm. The second example is using 'regexp' rather than "MATCH (...) AGAINST", so isn't actually doing fulltext boolean searches.

Are you sure you have "Extended Search All" set in the Filters tab for the List settings on the working site?

With that set to No, we'll use the simpler "regexp" search, rather than the "MATCH (...) AGAINST ('...' IN BOOLEAN MODE)", which provides those additional option (any terms, all terms, etc). And the 'regexp' method will work with those CONCAT_WS() subqueries for multi-select joins.

So I think your new site would work if you turned extended search all off.

-- hugh

 

[lcollong]

On both sites it was switched off. I've turned it on on the working site and it's still working. Both setup are included herein. The resulted SQL on the working site seems to be the same (after clearing all caches) with the search all on as here under.
If searching for a real word part of the multi-select DD (type_compagnie actually stored in the f_compagnies_maritimes_repeat_type_compagnie), it shows up a list which looks the right one... (tested vith "naval" as part of "chantier naval" - ship builder).

SELECT SQL_CALC_FOUND_ROWS DISTINCT `f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea`,
`f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea_raw`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id_raw`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie_raw`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame_raw`,
`f_compagnies_maritimes`.`fk_pays` AS `f_compagnies_maritimes___fk_pays_raw`,
`f_pays`.`nom_fr_FR` AS `f_compagnies_maritimes___fk_pays`,
`f_compagnies_maritimes`.`fk_ville` AS `f_compagnies_maritimes___fk_ville_raw`,
`f_villes`.`nom_ville` AS `f_compagnies_maritimes___fk_ville`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet_raw`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo_raw`,
(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_raw`, (SELECT GROUP_CONCAT(type_compagnie SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie_id,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie___params`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone_raw`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis_raw`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut_raw`,
`f_compagnies_maritimes`.`parent` AS `f_compagnies_maritimes___parent_raw`,
`f_compagnies_maritimes_0`.`nom_compagnie` AS `f_compagnies_maritimes___parent`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe_raw`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps_raw`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address_raw`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address_raw`,
CONCAT_WS(':', `f_compagnies_maritimes`.`id`, `f_compagnies_maritimes`.`nom_compagnie`) AS slug
, `f_compagnies_maritimes`.`id` AS `__pk_val`

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE ( (  LOWER(`f_compagnies_maritimes`.`nom_compagnie`) regexp LOWER('test')  OR  LOWER(`f_pays`.`nom_fr_FR`) regexp LOWER('test') OR  LOWER(`f_villes`.`nom_ville`) regexp LOWER('test') OR   LOWER(`f_compagnies_maritimes`.`site_internet`) regexp LOWER('test')  OR  LOWER((SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`)) regexp LOWER('test') OR   LOWER(`f_compagnies_maritimes`.`compagnie_groupe`) regexp LOWER('test') ) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC

 

[cheesegrits]

I can't replicate this. If I enable "Extended Search All", then it specifically excludes any multi-select elements (like checkbox joins) from the search. So it never even tries to do a MATCH AGAINST the funky CONCAT_WS() subquery. And stepping through the code, I remember adding that test, for exactly that reason, in the main includeInSearchAll() method. If I turn Extended Search All off, then it includes the checkbox join, but does a 'regexp' test instead of the MATCH AGAINST.

So just to confirm, if you turn Extended Search All off (and disable caching, under the Advanced tab, just to make sure you aren't hitting the query cache), do you still get that error with the MATCH AGAINST?

-- hugh

 

[lcollong]

Confirmed :
"Extended Search All" is (and was) turned off. "no" is the default value. I think I have never used it. Never switch it to "yes".
And yes, on the "new site" (new Fabrik version under J3.7.2), I still have this sql error involving the MATCH AGAINST error on the closing quote of the group_concat's separator part of "lookup.nom_type_co"

But :
If I switch "Extended Search all" on (yes) it does no longer throw any errors but it does not search in the "type compagnie" field, thus producing incomplete result with a "real" word. Here is the generated sql :

SELECT SQL_CALC_FOUND_ROWS DISTINCT `f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea`,
`f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea_raw`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id_raw`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie_raw`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame_raw`,
`f_compagnies_maritimes`.`fk_pays` AS `f_compagnies_maritimes___fk_pays_raw`,
`f_pays`.`nom_fr_FR` AS `f_compagnies_maritimes___fk_pays`,
`f_compagnies_maritimes`.`fk_ville` AS `f_compagnies_maritimes___fk_ville_raw`,
`f_villes`.`nom_ville` AS `f_compagnies_maritimes___fk_ville`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet_raw`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo_raw`,
(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_raw`, (SELECT GROUP_CONCAT(type_compagnie SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS f_compagnies_maritimes___type_compagnie_id,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie___params`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone_raw`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis_raw`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut_raw`,
`f_compagnies_maritimes`.`parent` AS `f_compagnies_maritimes___parent_raw`,
`f_compagnies_maritimes_0`.`nom_compagnie` AS `f_compagnies_maritimes___parent`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe_raw`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps_raw`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address_raw`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address_raw`,
`f_compagnies_maritimes`.`id` AS slug
, `f_compagnies_maritimes`.`id` AS `__pk_val`

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE (  (MATCH(`f_compagnies_maritimes`.`nom_compagnie`) AGAINST ('naval*' IN BOOLEAN MODE) OR MATCH( `f_pays`.`nom_fr_FR`) AGAINST ('naval*' IN BOOLEAN MODE) OR MATCH( `f_villes`.`nom_ville`) AGAINST ('naval*' IN BOOLEAN MODE) OR MATCH( `f_compagnies_maritimes`.`site_internet`, `f_compagnies_maritimes`.`compagnie_groupe`) AGAINST ('naval*' IN BOOLEAN MODE)) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC

So for some reason, in my setup, with "extended search all" set to "no", it does a MATCH AGAINST instead of the expected regex stuff ?

On the "old site", if I set "extended search all" to "yes" and disable cache to "yes" and clear all caches, I still have the right result and the following sql (I had to cut the beginning of the query due to post message size limitation) :

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE ( (  LOWER(`f_compagnies_maritimes`.`nom_compagnie`) regexp LOWER('naval')  OR  LOWER(`f_pays`.`nom_fr_FR`) regexp LOWER('naval') OR  LOWER(`f_villes`.`nom_ville`) regexp LOWER('naval') OR   LOWER(`f_compagnies_maritimes`.`site_internet`) regexp LOWER('naval')  OR  LOWER((SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`)) regexp LOWER('naval') OR   LOWER(`f_compagnies_maritimes`.`compagnie_groupe`) regexp LOWER('naval') ) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC

 

[cheesegrits]

Yup, I don't get it. If you look in ...

components/com_fabrik/models/listfilter.php

... at line 350 ...

https://github.com/Fabrik/fabrik/blob/master/components/com_fabrik/models/listfilter.php#L350

... if 'search-mode-advanced' is not set, we should call insertSearchAllIntoFilters(), which does the 'regexp', instead of doBooleanSearch(), which does the MATCH AGAINST.

So with search-mode-advanced ("Extended Search All") set to No, we should never call the code that builds those MATCH AGAINST queries.

-- hugh

 

[lcollong]

Indeed. Here is the code in my local rep starting line 347 :

        // Check that we actually have the correct list id (or -1 if filter from viz)
        if ($this->listModel->getTable()->id == $listId || $listId == -1)
        {
            if ($this->listModel->getParams()->get('search-mode-advanced'))
            {
                $this->doBooleanSearch($filters, $search);
            }
            else
            {
                $this->insertSearchAllIntoFilters($filters, $search);
            }
        }

But, I have a doubt.....
From the start, I'm speaking about the Joomla search. Not the "search all" from Fabrik available at the list level. Are they both trigger the same logic ?

If I set "search all" to "yes", which was not set before, the fabrik's search works very well and answers includes "type compagnie". But the joomla search produces the error we know.
If I set "extended search all" to "yes" as well, the fabriks's search does not produce error but does not find the right rows. As you point out, it does not search inside the repeated DBjoin "type of compagnie" thus missing two rows.
If I set back "search all" to "no" leaving "extended search all" to "yes", the fabrik's search field is, of course, not available. The Joomla search does not throw any error but does not show the right result (missing to search in the "type compagnie" column.
If I set back both switch to "no" as it was originally and as it is on the working site, the Joomla search generate the famous error.

On the "old site", with both switches set to "no", the Joomla search produces the right result including these lines with the word in the "type compagnie" column from the repeat table joined. The listfilter.php file on this site contains exactly the same instructions exactly at the same line numbers.

Do you want me make another github update on the "new site" ? wget from ssh still a safe method ?

 

[cheesegrits]

The Joomla search uses (or should use) the same logic for deciding whether to use regexp or boolean (match against), which is that "extended" option. It ignores the "search all". Basically the Joomla search is the same code as "search all", it's just run on multiple lists.

Which is why I'm really confused about the results you are seeing. With "extended" set to No, neither individual list "search all" or the Joomla global search should use the boolean method, and should include multi-select joins.

So yes, do a github update. Yes, wget from ssh should still be safe.

If you still have issues, I'll prolly need to get my hands on your site and throw some debug code in.

Sent from my HTC6545LVW using Tapatalk

 

[lcollong]

I think I have found it.
I've uninstalled the ArtioSEF component I had installed to try to manage some nicer url (with poor success). After the github update and the uninstall of this extension :
Everything is working as it was on the previous site with the right SQL :

SELECT SQL_CALC_FOUND_ROWS DISTINCT `f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea`,
`f_compagnies_maritimes`.`date_crea` AS `f_compagnies_maritimes___date_crea_raw`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id`,
`f_compagnies_maritimes`.`id` AS `f_compagnies_maritimes___id_raw`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie`,
`f_compagnies_maritimes`.`nom_compagnie` AS `f_compagnies_maritimes___nom_compagnie_raw`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame`,
`f_compagnies_maritimes`.`time_frame` AS `f_compagnies_maritimes___time_frame_raw`,
`f_compagnies_maritimes`.`fk_pays` AS `f_compagnies_maritimes___fk_pays_raw`,
`f_pays`.`nom_fr_FR` AS `f_compagnies_maritimes___fk_pays`,
`f_compagnies_maritimes`.`fk_ville` AS `f_compagnies_maritimes___fk_ville_raw`,
`f_villes`.`nom_ville` AS `f_compagnies_maritimes___fk_ville`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet`,
`f_compagnies_maritimes`.`site_internet` AS `f_compagnies_maritimes___site_internet_raw`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo`,
`f_compagnies_maritimes`.`compagnie_logo` AS `f_compagnies_maritimes___compagnie_logo_raw`,
(SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie`,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_raw`,
(SELECT GROUP_CONCAT(type_compagnie SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie_id`,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie WHERE parent_id = `f_compagnies_maritimes`.`id`) AS `f_compagnies_maritimes___type_compagnie___params`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone`,
`f_compagnies_maritimes`.`icone` AS `f_compagnies_maritimes___icone_raw`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis`,
`f_compagnies_maritimes`.`ref_equasis` AS `f_compagnies_maritimes___ref_equasis_raw`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut`,
`f_compagnies_maritimes`.`compagnie_statut` AS `f_compagnies_maritimes___compagnie_statut_raw`,
`f_compagnies_maritimes`.`parent` AS `f_compagnies_maritimes___parent_raw`,
`f_compagnies_maritimes_0`.`nom_compagnie` AS `f_compagnies_maritimes___parent`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe`,
`f_compagnies_maritimes`.`compagnie_groupe` AS `f_compagnies_maritimes___compagnie_groupe_raw`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps`,
`f_compagnies_maritimes`.`compagnie_gps` AS `f_compagnies_maritimes___compagnie_gps_raw`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address`,
`f_compagnies_maritimes`.`formated_address` AS `f_compagnies_maritimes___formated_address_raw`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address`,
`f_compagnies_maritimes`.`address` AS `f_compagnies_maritimes___address_raw`,
`f_compagnies_maritimes`.`id` AS slug
, `f_compagnies_maritimes`.`id` AS `__pk_val`

FROM `f_compagnies_maritimes`
LEFT JOIN  `f_pays` AS `f_pays` ON `f_pays`.`alpha3` = `f_compagnies_maritimes`.`fk_pays`
LEFT JOIN  `f_villes` AS `f_villes` ON `f_villes`.`id` = `f_compagnies_maritimes`.`fk_ville`
LEFT JOIN  `f_compagnies_maritimes` AS `f_compagnies_maritimes_0` ON `f_compagnies_maritimes_0`.`id` = `f_compagnies_maritimes`.`parent`
WHERE ( (  LOWER(`f_compagnies_maritimes`.`nom_compagnie`) regexp LOWER('naval')  OR  LOWER(`f_pays`.`nom_fr_FR`) regexp LOWER('naval') OR  LOWER(`f_villes`.`nom_ville`) regexp LOWER('naval') OR   LOWER(`f_compagnies_maritimes`.`site_internet`) regexp LOWER('naval')  OR  LOWER((SELECT GROUP_CONCAT(lookup.nom_type_co  SEPARATOR '//..*..//') FROM f_compagnies_maritimes_repeat_type_compagnie
        LEFT JOIN f_types_compagnie AS lookup ON lookup.id = f_compagnies_maritimes_repeat_type_compagnie.type_compagnie WHERE f_compagnies_maritimes_repeat_type_compagnie.parent_id = `f_compagnies_maritimes`.`id`)) regexp LOWER('naval') OR   LOWER(`f_compagnies_maritimes`.`compagnie_groupe`) regexp LOWER('naval') ) )
ORDER BY  `f_compagnies_maritimes`.`nom_compagnie` ASC

I don't think you want to dig in the reason why this extension https://www.artio.net/joomla-extensions/joomsef was missing up the things ?
BTW, I'll post on a new thread regarding SEF as the sh404SEF extension is now 44$ ....
Thanks for your help.
Oh, and I've just renewed my sub as it has expired some days ago....

 

[cheesegrits]

No, I don't think I do want to dig into Artio SEF. Not without someone paying me by the hour.

I can't even begin to imagine how Artio was messing things up. That code, at least where it makes the decision about which filtering method to use, is very simple. It checks a parameter from the list settings. And I don't even have the beginnings of a clue as to how Artio could interfere with that.

Thanks for re-upping the sub.

-- hugh