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.

enter image description here

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.


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

Table : sys_forms

enter image description here

Table : sys_forms_in_groups

enter image description here

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,

     , f.FormName
     , f.FormCIPath
     , MAX(g.IsMenuLink) AS IsMenuLink
  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.
enter image description here

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.

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.