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
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 :