45 Posts
Jan
5 years ago
3
Topic

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

Get a Book for SEBLOD
4229 Posts
Kadministrator
5 years ago
2
Level 1

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

45 Posts
Jan
5 years ago
1
Level 2

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.

4229 Posts
Kadministrator
5 years ago
0
Level 3

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.

Get a Book for SEBLOD