Hi
I'm struggling with a Seblod list where I need to select all the users who belong to group 15 and not belong to group 14
Making a list of users just belonging to one group is easy with https://www.seblod.com/store/extensions/922
but the storage of the user_usergroup_map table makes it difficult to filter the users who belong to one group while not belonging to another.
Even with a user_group alias field the list I have gives :
SELECT t0.id AS pid,t1.group_id,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 `xfz57_cck_core` AS t0 LEFT JOIN `xfz57_content` AS t3 ON t3.id = t0.pkb LEFT JOIN `xfz57_users` AS t4 ON t4.id = t0.pk LEFT JOIN `xfz57_user_usergroup_map` AS t1 ON t1.user_id = t4.id LEFT JOIN `xfz57_usergroups` AS t2 ON t2.id = t1.group_id LEFT JOIN `xfz57_cck_core_types` AS tt ON tt.name = t0.cck LEFT JOIN `xfz57_cck_store_item_users` AS t5 ON t5.id = t0.pk WHERE t3.state = 1 AND t3.access IN (1,1,2,3,7,8,9,10,11,12,13,16) AND ( t3.publish_up = '0000-00-00 00:00:00' OR t3.publish_up <= '2022-01-26 09:15' ) AND ( t3.publish_down = '0000-00-00 00:00:00' OR t3.publish_down >= '2022-01-26 09:15' ) AND t0.cck = 'membre_dbf' AND t1.group_id = '15' AND t1.group_id != '14' ORDER BY t5.last_name ASC
but it fails to reach the goal since seblod sees each line of the user_usergroup_map table as a separate entry
any clue please?
thanks
Hi
Is there someone left on this forum please?
Can someone share his thoughts on this issue ?
thanks
ok
I will answer my own request. Maybe someone will find a better and smarter way to acheive this :
The idea is to perform a FREE SEARCH based on this canvas :
SELECT t1.user_id, t1.group_id
FROM `xfz57_user_usergroup_map` as t1
WHERE t1.group_id = 15
AND t1.user_id NOT IN (
SELECT t2.user_id FROM `xfz57_user_usergroup_map` as t2 WHERE t2.group_id = 14
)
But since I need to dynamically assign the INCLUSION group (here 15) and the EXCLUSION group (here 14) I would add a BEFORE SEARCH CODE plugin that will take the values of two fields (user_groups and user_groups _alias) and inject them in the query
the BEFORE SEARCH field will alter the FREE SQL with the follwing code :
$fields['search']["us_referents_freesearch"]->options2= ....
Any better solution ? I would gladly take !
Cyril
