Hi All,
To filter the results I use select dynamic with this query:
SELECT thema FROM dva_cck_store_form_werken
INNER JOIN dva_content
ON dva_content.id=dva_cck_store_form_werken.id
WHERE catid=12 AND thema!=''
ORDER BY thema ASC;
storage: table dva_cck_store_form_werken, field thema.
With the current version 3.17.1 of dynamic select this does not work.
Debugging gives this output:
SELECT t0.id AS pid,t0.pk AS pk,t0.pkb AS pkb,t0.parent_id AS parent,t0.author_id AS author,t0.author_session AS author_session,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
LEFT JOIN `#__cck_store_form_werken` AS t2 ON t2.id = t0.pk
WHERE t1.state = 1
AND t1.access IN (1,1,5)
AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2019-01-17 08:57' )
AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2019-01-17 08:57' )
AND t0.cck = 'werken'
AND t1.catid = 12
ORDER BY t2.volgnummer DESC
LIMIT 50
SELECT COUNT(t0.id)
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
LEFT JOIN `#__cck_store_form_werken` AS t2 ON t2.id = t0.pk
WHERE t1.state = 1
AND t1.access IN (1,1,5)
AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2019-01-17 08:57' )
AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2019-01-17 08:57' )
AND t0.cck = 'werken'
AND t1.catid = 12
When I use version 3.10 of dynamic select it works with this output:
SELECT t0.id AS pid,t0.pk AS pk,t0.pkb AS pkb,t0.parent_id AS parent,t0.author_id AS author,t0.author_session AS author_session,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_store_form_werken` AS t2 ON t2.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,5)
AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2019-01-17 08:51' )
AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2019-01-17 08:51' )
AND t0.cck = 'werken'
AND t1.catid = 12
AND t2.thema LIKE '%mens%'
ORDER BY t2.volgnummer DESC
LIMIT 50
I wonder if there is a better solution than using an old version.
Greetings,
Jan
Should still work. Try replacing "SELECT thema" with "SELECT thema as text, thema as value" and "dva_content" with "#__content" in your field query, if that makes any difference, otherwise there is something wrong with your storage settings
Thank you Klas for you response. It should work indeed, but it does not. You suggestions make no sense. The query and the storage are fine in V3.10. There must be something wrong in the versions after 3.10 and I have found it.
Version 3.10 has on line 490 and 491:
// Set
$field->value = $value;
In version 3.11 this lines are removed. If I insert this in version 3.17.1 after line 532, it works.
I tested the field and it works correctly. Your problem is your query is not written as they should be - you always need to define text and value properties in the query, it is cleary stated in the field help file.