Show if group has access to certain forms

Show if group has access to certain forms

Problem Description:

I’m trying to understand how to write a query to distinguish if a user has access to certain form or not. I know what I am asking here looks easy but when I tried to implement it, it was whole different thing.
Maybe I am doing it wrong.

Before starting I want to mention the two tables names in start:

1. sys_forms,
2. sys_forms_in_groups

Also I am putting a SQL Fiddle link at the end.

Here is what I am trying to do.

If you can see the picture, on top dropdown box (it’s a select2 dropdown), user selects the group and it will return GroupID, on the base of which I want to populated the below datatable. (DataTable is just showing groups, it’s a dummy, but it will show forms, will fix it if problem is solved)

Now here the problem arise:

I want datatables to show all the forms available in sys_forms table in datatables but in actions columns of datatables only those checkboxes/switches should show granted which are available in the selected group(Group can be selected for select2 dropdown as said before).

GroupID is the column of other table sys_forms_in_groups.

All I want is that all the forms should show in the datatables no matter what group I choose, but Actions column in table should display Granted if the group has access to that particular form.

forms_in_groups is for showing if group has access to that certain form or not. For example:

    FormID  GroupID
------------------------------
    1          1
    2          1
    1          2

FormID 1 is available to both groupID 1 and 2, on other hand FormID 2 is avaialable only to GroupID 1.

Here is my SQL Fiddle.

Edit

The SQL Fiddle is not working, so putting screenshots here.

Table : sys_forms

Table : sys_forms_in_groups

I have tried this query, but it only returns forms for the selected group, where I want that all forms should show but they must show granted in Actions Columns on checkboxes/switchButtons

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups` 
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID` WHERE `GroupID` = 1;

Solution – 1

I think you’re, incorrectly, trying to offload the issue onto MySQL.

It’s not MySQL’s job to render the forms and show whether a selected group has access to a specific form. That will fall onto your rendering of the page; using Select2 in this case.

You just want to retrieve all forms, then show whether the selected group has access to a specific form. That comes down to some Javascript that makes that check for you and displays it properly.

If you want to return all forms, the query would be:

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups`
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID`;

If you only want the forms that group 1 has access to, the query would be just as you have it:

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups` 
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID` WHERE `GroupID` = 1;

I didn’t do a lot of digging into Select2, so I can’t really help you in that aspect.

Solution – 2

What I can see from your database schema, an inner join is the correct choice here based on the data you are looking for. The only issue I can see is in the WHERE clause. Seems that it may try to look for GroupID in the sys_forms table, so try specifying.

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups` 
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID` WHERE
`sys_forms_in_groups`.`GroupID` = 1;

Solution – 3

Finally Problem Solved and working perfectly fine, but after solving that problem i found out there is an other issue i didnt think of, but thats another matter. xD.
However, It was a little rough way to solve but i got what i wanted.

Here is how i did it,

I first needed to update my select query to,

SELECT f.FormID
     , f.FormName
     , f.FormCIPath
     , MAX(g.IsMenuLink) AS IsMenuLink
     , GROUP_CONCAT(DISTINCT g.GroupID ORDER BY g.GroupID) AS GroupIDs
  FROM `sys_forms` f 
  JOIN `sys_forms_in_groups` g
    ON g.FormID = f.FormID
   AND g.GroupID IN (1,2)
 GROUP BY f.formID 

Here you can see it will return all the forms which belongs to group 1 and group 2 but in a way that 1 and 2 will be in same column separated by comma.

Here how it shows now.

I am not a very complex query master so i am very much grateful to stackoverflow community to help me with the query. As i wanted to join both results to show in comma separated value.

After the MysQL the jquery work was not much difficult, i only sent the group ID for which i wanted the result to show in table. and there i got this result in return where i separate the GroupIDs with javascript split function and i get my groups.

Thankyou again everyone.