I have the following tables in my DB:
CREATE TABLE `restricted_components` (
`component_id` int(11) NOT NULL,
`resources_id` int(11) NOT NULL,
`component_dom_id` varchar(50) NOT NULL,
`component_dom_data_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`component_id`),
UNIQUE KEY `component_id_UNIQUE` (`component_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `resources` (
`resources_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`resources_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`menu_href` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`menu_text` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`resources_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `acl_groups_to_resources` (
`groups_id` int(10) unsigned NOT NULL,
`resources_id` int(10) unsigned NOT NULL,
`bitmask` smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`groups_id`,`resources_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `groups` (
`groups_id` int(11) NOT NULL AUTO_INCREMENT,
`groups_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`groups_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `users_to_groups` (
`users_id` int(11) NOT NULL,
`groups_id` int(11) NOT NULL,
PRIMARY KEY (`users_id`,`groups_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `users` (
`users_id` int(11) NOT NULL AUTO_INCREMENT,
`parent_users_id` int(11) DEFAULT '0',
`firstname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`lastname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`users_id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
I need to get the unique acl_groups_to_resources.bitmask
value having the restricted_components.component_dom_id
, resources.menu_href
, users.users_id
, [restricted_components.component_dom_data_name
] (this last one is optional see the OR
on the SQL).
Coming from this post and tweaking it a bit I end up with the following SQL:
SELECT
`rc`.`component_id`,
`acl_gr`.`resources_id`,
`acl_gr`.`bitmask`
FROM `restricted_components` AS `rc`
LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
INNER JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
INNER JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id
INNER JOIN `users_to_groups` AS `usr_gr` ON gr.groups_id = usr_gr.groups_id
INNER JOIN `users` AS `usr` ON usr_gr.users_id = usr.users_id
WHERE (rc.component_dom_id = 'add_component_restriction')
AND (rs.menu_href = 'adminconsole')
AND (usr.users_id = 976)
OR (rc.component_dom_data_name = 'btn_add_restriction')
The query above works but I am getting two results instead of one:
component_id | resources_id | bitmask
--------------------------------------
1 | 129 | 1
1 | 129 | 15
A correct output would be a unique row containing the bitmask
value for the given parameters. Something is wrong on the query but I am not sure what it's so I need more eyes on this.
This is the current data on each table (maybe the problem is on the data):
restricted_components
=====================
component_id | resources_id | component_dom_id | component_dom_data_name
----------------------------------------------------------------------------------
1 | 129 | add_component_restriction
2 | 129 | edit_component_restriction
3 | 129 | delete_component_restriction
resources
=====================
resources_id | resources_name | menu_href | menu_text
-------------------------------------------------------------
129 | Add | adminconsole | Admin Console
acl_groups_to_resources
=====================
groups_id | resources_id | bitmask | created_by_users_id
--------------------------------------------------------
1 | 129 | 1 | 976
2 | 129 | 15 | 976
3 | 129 | 0 | 976
groups
=====================
groups_id | groups_name
---------------------------
1 | Administrators
2 | Restricted Admin
3 | Others
users_to_groups
======================
users_id | groups_id
---------------------------
976 | 1
976 | 2
users
=====================
users_id | parent_users_id | firstname | email
-------------------------------------------------------------
976 | 0 | Dev | userdev@email.comdev
Can any give me some help on this? (I am using MySQL 5.5)
Note: Feel free to rewrite the SQL if needed in order to obtain the required results.
Best Answer
JOINs
tend to get more and more rows.You probably need to combine the 'dup' rows and use
BIT_OR()
to shrink back to one row: