Nyranith Nyranith - 3 months ago 11
MySQL Question

Select multiple min values from subset of values

I am creating an access system. The access system is based on that a user can be a member of more than one group, the domain is where the user ask for access. For example an user can be a member of two groups, both these groups can have access to the domain and the user can be a member of the domain. I want the lowest access right to be the valid one, say you get added to a group that have lower right in that domain that right should precede the other right.

The problem is that i want to select each domain and get all the access rights for each domain.
So the problem is to select a min value from multiple subset. Got an idea to how to do this ?

So i have tried :

SELECT r.* FROM `domain` as d
INNER JOIN (
SELECT domain, min(`update`) FROM `right`
) r ON d.name = r.domain;


This gives me the result :

domain min(
update
)

Another test true

What i want is :

domain min(
update
)

Another test true

Some Name true

Sql fiddle link : http://sqlfiddle.com/#!9/841183

Export of the sql table with test values:

CREATE TABLE `domain` (
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`displayName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `domain`
ADD PRIMARY KEY (`name`);

CREATE TABLE `groups` (
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_by` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `groups`
ADD PRIMARY KEY (`name`),
ADD KEY `groups_created_by_foreign` (`created_by`);


CREATE TABLE `right` (
`domain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`group` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`read` tinyint(1) NOT NULL,
`update` tinyint(1) NOT NULL,
`create` tinyint(1) NOT NULL,
`delete` tinyint(1) NOT NULL,
`modify` tinyint(1) NOT NULL,
`execute` tinyint(1) NOT NULL,
`created_by` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `right`
ADD PRIMARY KEY (`domain`,`group`),
ADD KEY `right_group_foreign` (`group`),
ADD KEY `right_created_by_foreign` (`created_by`);


INSERT INTO `domain` (`name`, `displayName`, `description`, `created_at`, `updated_at`) VALUES
('Another test', 'Another test', NULL, '2016-08-22 12:05:06', '2016-08-22 12:05:06'),
('Some Name', 'Some Name', NULL, '2016-08-19 03:57:00', '2016-08-19 03:57:00');


INSERT INTO `groups` (`name`, `created_by`, `created_at`, `updated_at`) VALUES
('groupTest', NULL, '2016-08-22 12:06:48', '2016-08-22 12:06:48'),
('testGroup', NULL, '2016-08-19 03:56:35', '2016-08-19 03:56:35');


INSERT INTO `right` (`domain`, `group`, `read`, `update`, `create`, `delete`, `modify`, `execute`, `created_by`, `created_at`, `updated_at`) VALUES
('Another test', 'groupTest', 0, 1, 0, 0, 0, 0, NULL, '2016-08-22 12:07:02', '2016-08-22 12:07:02'),
('Another test', 'TestGroup', 1, 1, 0, 0, 0, 0, NULL, '2016-08-22 12:06:04', '2016-08-22 12:06:04'),
('Some Name', 'TestGroup', 0, 1, 0, 0, 0, 0, NULL, '2016-08-19 03:57:05', '2016-08-19 03:57:05');

Answer

You need to use the group by clause to get the minimum values by groups. By groups here I mean the domains:

select d.name, min(`update`) as min_update_right
from domain d
inner join `right` r on d.name = r.domain
group by d.name

If you want additional fields from the domain table to be present in the select list, then you need to add those fields to the group by list as well.

However, I would consider using a bitmap (or in MySQL the set data type) instead of multiple fields to store access rights. Simple bitwise operations could give you the overall access right to a domain.