SelectItem with PickList. How to do the following?

[Kah0ona]

Hello.

I have three MySQL tables relevant to my challenge:

users(username, ...) -----> corresponds to users.ds.xml
groups(group_id, group_name) -----> corresponds to groups_table.ds.xml
group_members(group_id,username) ------> corresponds to group_members.ds.xml

As you can see, this is a typical group-based user-rights story.
I have a ListGrid connected to users.ds.xml, that shows the users in my system. I want to have a groups column where I can change the membership of the users, with the pull-down-with-checkboxes-widget (see screenshot below for what I mean).

I am getting close to making this work, but not completely there yet. It is the client-side that I cannot completely do, the server-side part I'll manage.

My users.ds.xml

DataSource ID="users" serverType="sql" tableName="users"> 
  <fields> 
    <field name="username" type="text" length="50" primaryKey="true" required="true" />  
    <field name="email" type="text" required="true"/>  
    <field name="password" type="text" length="50"/>  
    <field name="salt" type="hidden"/>  
    <field name="account_enabled" type="boolean" required="false"/>  
    <field name="list_groups" type="text" customSQL="true" tableName="groups"/> 
  </fields>  
  <operationBindings> 
     <operationBinding operationType="fetch" customValueFields="list_groups" customCriteriaFields="list_groups" operationId="getGroupsForTheSelectedUser"> 
      <selectClause>users.username, 
                    users.password, 
                    users.salt,
                    users.account_enabled,
                    GROUP_CONCAT(DISTINCT groups.group_name ORDER BY groups.group_name ASC SEPARATOR ', ') as list_groups 
      </selectClause>  
      <tableClause>users, groups, group_members</tableClause>  
      <whereClause>group_members.group_id = groups.id AND users.username=group_members.username AND ($defaultWhereClause)</whereClause>
      <groupClause>users.username</groupClause>         
    </operationBinding>     
  </operationBindings> 
</DataSource>

my groups.ds.xml / group_members.ds.xml are trivial:

<DataSource ID="groups_table" serverType="sql" tableName="groups"> 
  <fields> 
    <field name="id" type="sequence" required="true" primaryKey="true"/>  
    <field name="group_name" type="text" length="50" required="true"/> 
  </fields> 
</DataSource>

<DataSource ID="group_members" serverType="sql" tableName="group_members"> 
  <fields> 
    <field name="group_id" type="integer" foreignKey="groups_table.id" required="true"/>  
    <field name="username" type="text" foreignKey="users.username" length="50" required="true"/> 
  </fields> 
</DataSource>

As you can see, in users.ds.xml I used SQL templating which creates a comma-separated-list of groups the user is member of (using GROUP_CONCAT() function from MySQL). So if user 'abc' is member of groups 'admins' and 'publishers', it shows correctly in the grid as : 'admins,publishers'. If I now want to edit this field, and click on the pulldown menu, the groups the user is member are not selected, that is, all is deselected. Unless: the user is member of only one group; then pressing the pull down menu actually shows it correctly. This is the view part of my code

UserManagement.java:

ListGridField groupsField = new ListGridField("list_groups",
				"Groups");
		
SelectItem sel = new SelectItem("list_groups","Groepen");
sel.setMultiple(true);
sel.setMultipleAppearance(MultipleAppearance.PICKLIST);
sel.setOptionDataSource(DataSource.get("groups_table"));
sel.setValueField("group_name");
sel.setDisplayField("group_name");		

groupsField.setEditorType(sel);

The question is: how can I make this work. Is grouping on the fetch operation the correct way to go, as users.ds.xml illustrates? Any tips welcome!

Also attached is a screenshot how it currently looks.

[Kah0ona]

Hm... no one?

[Kah0ona]

I resolved it by abandoning SQL-templating, and creating my own fetch/add/update/remove methods on the server and use DMI instead. Works fine now.

[tmoes]

Hi Kah0ona,

can you post your complete solution (gui,ds and dmi-classes), the GUI looks great.

I would be very happy.
Cheers,
Timo

[Kah0ona]

Well the ds files look like this:

<DataSource ID="users" serverType="sql" tableName="users"> 
  <fields> 
    <field name="username" type="text" length="50" primaryKey="true" required="true" /> 
    <field name="email" type="text" required="true"/>  
    <field name="password" type="text" length="50"/>  
    <field name="salt" type="hidden"/>  
    <field name="account_enabled" type="boolean" required="false"/>  
    <field name="list_groups" type="text"  length="50" customSQL="true"/> 
  </fields>  
  
  <operationBindings> 
    <operationBinding operationType="add" requiresRole="ROLE_SUPER_ADMINISTRATOR" requiresAuthentication="true"> 
      <serverObject lookupStyle="spring" bean="jdbcUserService"/> 
    </operationBinding>  
    <operationBinding operationType="fetch" requiresRole="ROLE_SUPER_ADMINISTRATOR" requiresAuthentication="true" operationId="getGroupsForTheSelectedUser"> 
      <serverObject lookupStyle="spring" bean="jdbcUserService"/> 
    </operationBinding>
    <operationBinding operationType="update" requiresRole="ROLE_SUPER_ADMINISTRATOR" requiresAuthentication="true"> 
      <serverObject lookupStyle="spring" bean="jdbcUserService"/> 
    </operationBinding>  
  </operationBindings> 
</DataSource>

As you can see I reference a spring bean, which has add,remove,update,fetch methods.

and the UI code is the same as above.

The DMI methods are just your average DAO classes, doing some queries with joins. Return your data in your DSResponse in your fetch() method as a List<Map<String,Object>>, where each Map<String,Object> entry is a user (make sure that the list_groups map entry is a List<String> again, which will contain the groups for a particular user).

If you return such a datastructure in your DSResponse from your fetch method, the client-side will display it correctly. My fetch() looks like this:

public DSResponse fetch(DSRequest req){
  List<Map<String,Object>> users;
  users = getAllUsersAndTheirGroupsAsArray(); // <--- implement this for your situation
  DSResponse r = new DSResponse();

  r.setData(users); 
  return r;
}