Hi there,
I am trying to build a search based on the Author Metadata:
JSON > article > metadata[author]
The field value saves and displays as it should in content but breaks when added to a list and search.
The search string builds ok eg: &art_author=test&search=my_content_type&task=search but the page always has an sql error.
Do I need to add staging to the search form or are JSON field values not searchable?
Thx.
My filed is stored in a new column named "seblod_cck". The storage of field that stores the items is set to JSON->Article->seblod_cck[sponsor_years]. In the DB in the column seblod cck the right content is set: e.g. {"sponsor_years":"egn12,egn13,egn14"}. So the storage works.
If I search with a simple text field that is set to the same storage type (JSON->Article->seblod_cck[sponsor_years]), I get the following SQL error:
"1064 - 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 'GROUP BY t0.pk ORDER BY t1.ordering ASC' at line 6 SQL=SELECT t0.id as pid, t0.pk as pk, t0.pkb as pkb,t0.cck as cck, t0.storage_location as loc,tt.id AS type_id, tt.alias AS type_alias FROM `egn_cck_core` AS t0 LEFT JOIN `egn_content` AS t1 ON t1.id = t0.pk LEFT JOIN `egn_cck_core_types` AS tt ON tt.name = t0.cck WHERE t1.state = 1 AND t1.access IN (1,1,2,3) AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2014-03-07 09:51:47' ) AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2014-03-07 09:51:47' ) AND t0.cck = 'sponsor' AND GROUP BY t0.pk ORDER BY t1.ordering ASC"
If I change the storage type to standard and the column to "seblod_cck" the search works: (the searchfield is set to "egn14")
"SELECT t0.id as pid, t0.pk as pk, t0.pkb as pkb,t0.cck as cck, t0.storage_location as loc,tt.id AS type_id, tt.alias AS type_alias FROM `#__cck_core` AS t0 LEFT JOIN `#__content` AS t1 ON t1.id = t0.pk LEFT JOIN `#__cck_core_types` AS tt ON tt.name = t0.cck WHERE t1.state = 1 AND t1.access IN (1,1,2,3) AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up = '2014-03-07 09:53:38' ) AND t0.cck = 'sponsor' AND t1.seblod_cck LIKE '%egn14%' GROUP BY t0.pk ORDER BY t1.ordering ASC"
Due to the fact that I want to use the column for many JSON fields I want to be able to search with a JSON field. Is there a solution for the problem?
Hi,
Did you find a solution? I have a problem with tag search. I tried to use a JSON article|metadata[tags] fiekd as a workaround. It works for storage but I get an SQL error when using the field in a search (there are two consecutive "AND" in the query where the column and values should be).
So I'm stuck that way too.
Help appreciated.
Xav
