Edit rights

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
baudwalker
Veteran Member
Posts: 119
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Edit rights

Post by baudwalker » 2019-05-03 07:12

Is there a quick way to find the number of members who have edit right on a given table. This is a once off so just some SQL should do the trick.

Many thanks

Barry

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 264
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Edit rights

Post by jsetzer » 2019-05-03 08:49

Hi Barry,

sure, have a look and try the following SQL command as a starting point:

Code: Select all

select 
membership_grouppermissions.tablename as 'tablename', 
count(*) as 'num_editors', 
group_concat(membership_users.memberID ORDER BY membership_users.memberID SEPARATOR ', ') as 'editors'
from membership_grouppermissions 
inner join membership_groups on membership_grouppermissions.groupID=membership_groups.groupID
inner join membership_users on membership_groups.groupID = membership_users.groupID
where membership_grouppermissions.allowEdit
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', YOURTABLE_2')
group by tablename
order by 1
Executed on your database it should give you a result like this:

chrome_2019-05-03_10-43-23.png
chrome_2019-05-03_10-43-23.png (3.34 KiB) Viewed 647 times

If you only need one table, replace the line...

Code: Select all

AND membership_grouppermissions.tablename IN ('YOURTABLE_1', YOURTABLE_2')
by ...

Code: Select all

AND membership_grouppermissions.tablename IN ('YOURTABLE_1')
or by...

Code: Select all

AND membership_grouppermissions.tablename = 'YOURTABLE_1'
Hope this helps!

Best Regards,
Jan

User avatar
baudwalker
Veteran Member
Posts: 119
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Edit rights

Post by baudwalker » 2019-05-04 00:23

Thank you Jan,

worked perfect once I put the extra ' in YOUTABLE_2' ('YOURTABLE_1', YOURTABLE_2') > ('YOURTABLE_1', 'YOURTABLE_2').

great quick response

Barry

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 264
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Edit rights

Post by jsetzer » 2019-05-05 10:30

My typo, sorry!

Cannot edit the original post, so here is the updated SQL code:

Code: Select all

select 
membership_grouppermissions.tablename as 'tablename', 
count(*) as 'num_editors', 
group_concat(membership_users.memberID ORDER BY membership_users.memberID SEPARATOR ', ') as 'editors'
from membership_grouppermissions 
inner join membership_groups on membership_grouppermissions.groupID=membership_groups.groupID
inner join membership_users on membership_groups.groupID = membership_users.groupID
where membership_grouppermissions.allowEdit
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', 'YOURTABLE_2')
group by tablename
order by 1
Replace YOURTABLE_1 and YOURTABLE_2.

Regards,
Jan

User avatar
baudwalker
Veteran Member
Posts: 119
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Edit rights

Post by baudwalker » 2019-05-05 23:59

'YOURTABLE_1' , ' YOURTABLE_2' worked fine

Thank you

PS... just to be difficult, what if I required those who had edit right to both not just either?

Barry

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 628
Joined: 2018-04-01 10:12

Re: Edit rights

Post by pböttcher » 2019-05-06 05:10

use

AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')

User avatar
baudwalker
Veteran Member
Posts: 119
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Edit rights

Post by baudwalker » 2019-05-06 05:43

Great Thank you

User avatar
baudwalker
Veteran Member
Posts: 119
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Edit rights

Post by baudwalker » 2019-05-06 09:50

Hi Pascal.
No, AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')
Didn't work.
I have edit rights to all table and when I run the sql on a single table it works file but when I use it using your code for two tables I get

" MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 seconds.) "

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 628
Joined: 2018-04-01 10:12

Re: Edit rights

Post by pböttcher » 2019-05-06 21:29

oh, sorry, I guess I was to short in my answer.

Try this

Code: Select all

select group_concat(membership_grouppermissions.tableName ORDER BY membership_grouppermissions.tableName SEPARATOR ', ') as tablename, membership_users.memberid FROM `membership_grouppermissions` left JOIN membership_users on membership_users.groupID=membership_grouppermissions.groupID where membership_grouppermissions.allowEdit and tableName in ('YOURTABLE_1','YOURTABLE2') group by membership_users.memberid HAVING count(membership_grouppermissions.groupid) = 2
where the count(membership_grouppermissions.groupid) = 2 needs to match number of tables you are putting in the ('YOURTABLE_1','YOURTABLE2')

User avatar
baudwalker
Veteran Member
Posts: 119
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Edit rights

Post by baudwalker » 2019-05-06 23:36

Perfect,
It is me how should be sorry for my lack of knowledge. I just replaced the line in Jan's code with the line you supplied.

My thanks goes out to the both of you,

Barry

Post Reply