Odczytywanie praw dostępu bez zdublowanych rekordów, Problem z średnio skomplikowanym zapytaniem Opcje

[Jarod]

Chciałbym otrzymać: account_id, login, password, blocked, expired, nazwy wszystkich grup do których należy użytkownik i wszystkie role z tych poszczególnych grup (bez powtórzeń). Jak to zrobić?

Kombinuje tak ale może da się inaczej/szybciej...

[SQL] pobierz, plaintext 
SELECT account_id, login, password, blocked, expired, group_name, role_name FROM accounts INNER JOIN accounts_groups USING(account_id) INNER JOIN groups_roles USING(group_id) INNER JOIN groups USING(group_id) INNER JOIN roles USING (role_id);
[SQL] pobierz, plaintext 

A tutaj gotowa struktura bazy do testów:

Kod wycięty, poprawiony i kompletny poniżej.

Ten post edytował Jarod 18.04.2007, 19:35:56

[kukoc]

wlasnie jestem na etapie tworzenia praw dostepu dla mojej zabawki, i mniej wiecej ten sam problem mi sie pojawil, troche sie uhahałem z tym ale tu masz rozwiazanie

pomogla ekipa z kanalu #php.pl

[SQL] pobierz, plaintext 
SELECT accounts.account_id, login, password, blocked, expired,
GROUP_CONCAT(DISTINCT group_name) AS 'grupy' , GROUP_CONCAT(DISTINCT role_name) AS 'role'
FROM groups_roles, roles, accounts_groups, accounts, groups 
 
WHERE groups_roles.role_id = roles.role_id AND groups_roles.group_id = accounts_groups.group_id AND accounts.account_id = accounts_groups.account_id AND groups.group_id = accounts_groups.group_id
 
GROUP BY accounts_groups.account_id
[SQL] pobierz, plaintext 

swoja droga w zrzucie tabel brakowalo ci roli13, co troszeczke mnie zmylilo (takie 15 min kombinowania)

Ten post edytował kukoc 17.04.2007, 14:32:39

[Jarod]

@kukoc: wielkie dzięki bo to nie dawało mi spokoju - już myślałem że jest to nie możliwe i chciałem kombinować z podzapytaniami (raczej mało wydajne).

Mam jeszcze jedno pytanie: wiesz jak to przerobić na JOIN?

Tak czy inaczej masz wirtualnego browca (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[kukoc]

sorry nie wiem tak na szybko... (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[Jarod]

Problem podobny jak wcześniej tylko, że dochodzi nam tablica z parametrami. Dla konta jas powinno się otrzymać wynik:

Kod

jas | fsola | 0 | NULL | Grupa2, Grupa3 | Role7,Role8(3),Role10,Role12, Role7,Role8(5),Role10,Role12 |

Jak napisać takie zapytanie? Poniżej pełny kod SQL - wystarczy wkleić do mysql

[SQL] pobierz, plaintext 
CREATE DATABASE acl;
USE acl;
 
CREATE TABLE sessions
(
session_id INT NOT NULL AUTO_INCREMENT,
session_identifier CHAR(32) NOT NULL,
session_time_start DATETIME NOT NULL,
session_last_time DATETIME NOT NULL,
address_ip VARCHAR(15) NOT NULL,
login VARCHAR(30),
session_values TEXT,
UNIQUE(session_identifier),
PRIMARY KEY(session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE groups
(
group_id INT NOT NULL AUTO_INCREMENT,
group_name VARCHAR(30) NOT NULL,
group_description VARCHAR(60) NOT NULL,
PRIMARY KEY(group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE roles
(
role_id INT NOT NULL AUTO_INCREMENT,
role_name VARCHAR(30) NOT NULL,
role_description VARCHAR(60) NOT NULL,
PRIMARY KEY(role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE parameters
(
parameter_id INT NOT NULL AUTO_INCREMENT,
parameter_name VARCHAR(30) NOT NULL,
PRIMARY KEY(parameter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE accounts
(
account_id INT NOT NULL AUTO_INCREMENT,
login VARCHAR(20) NOT NULL,
password CHAR(40) NOT NULL,
blockade BOOLEAN NOT NULL DEFAULT false,
password_update DATE,
UNIQUE(login),
PRIMARY KEY(account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE accounts_groups
(
account_id INT NOT NULL,
group_id INT NOT NULL,
INDEX(account_id),
INDEX(group_id),
CONSTRAINT fkey_accounts_groups_account_id FOREIGN KEY(account_id) REFERENCES accounts(account_id),
CONSTRAINT fkey_accounts_groups_group_id FOREIGN KEY(group_id) REFERENCES groups(group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE groups_roles
(
group_id INT NOT NULL,
role_id INT NOT NULL,
INDEX(group_id),
INDEX(role_id),
CONSTRAINT fkey_groups_roles_group_id FOREIGN KEY(group_id) REFERENCES groups(group_id),
CONSTRAINT fkey_groups_roles_role_id FOREIGN KEY(role_id) REFERENCES roles(role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE groups_roles_parameters
(
group_id INT NOT NULL,
role_id INT NOT NULL,
parameter_id INT NOT NULL,
INDEX(group_id),
INDEX(role_id),
INDEX(parameter_id),
CONSTRAINT fkey_groups_roles_parameters_group_id FOREIGN KEY(group_id) REFERENCES groups(group_id),
CONSTRAINT fkey_groups_roles_parameters_role_id FOREIGN KEY(role_id) REFERENCES roles(role_id),
CONSTRAINT fkey_groups_roles_parameters_parameter_id FOREIGN KEY(parameter_id) REFERENCES parameters(parameter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
 
 
 
 
INSERT INTO groups VALUES(NULL, 'Grupa1', 'Opis1');
INSERT INTO groups VALUES(NULL, 'Grupa2', 'Opis2');
INSERT INTO groups VALUES(NULL, 'Grupa3', 'Opis3');
INSERT INTO groups VALUES(NULL, 'Grupa4', 'Opis4');
 
 
INSERT INTO roles VALUES(NULL, 'Role1',  'Opis1');
INSERT INTO roles VALUES(NULL, 'Role2',  'Opis2');
INSERT INTO roles VALUES(NULL, 'Role3',  'Opis3');
INSERT INTO roles VALUES(NULL, 'Role4',  'Opis4');
INSERT INTO roles VALUES(NULL, 'Role5',  'Opis5');
INSERT INTO roles VALUES(NULL, 'Role6',  'Opis6');
INSERT INTO roles VALUES(NULL, 'Role7',  'Opis7');
INSERT INTO roles VALUES(NULL, 'Role8',  'Opis8');
INSERT INTO roles VALUES(NULL, 'Role9',  'Opis9');
INSERT INTO roles VALUES(NULL, 'Role10', 'Opis10');
INSERT INTO roles VALUES(NULL, 'Role11', 'Opis11');
INSERT INTO roles VALUES(NULL, 'Role12', 'Opis12');
INSERT INTO roles VALUES(NULL, 'Role13', 'Opis13');
 
 
INSERT INTO parameters VALUES(NULL, '*');
INSERT INTO parameters VALUES(NULL, 'param1');
INSERT INTO parameters VALUES(NULL, 'param2');
INSERT INTO parameters VALUES(NULL, 'param3');
INSERT INTO parameters VALUES(NULL, 'param4');
INSERT INTO parameters VALUES(NULL, 'param5');
INSERT INTO parameters VALUES(NULL, 'param6');
INSERT INTO parameters VALUES(NULL, 'param7');
 
 
INSERT INTO accounts VALUES(NULL, 'admin', 'pass', 0, NULL);
INSERT INTO accounts VALUES(NULL, 'jas', 'fasola', 0, NULL);
INSERT INTO accounts VALUES(NULL, 'ptasiek', 'maly', 0, NULL);
INSERT INTO accounts VALUES(NULL, 'mod', 'mod', 0, NULL);
 
 
INSERT INTO accounts_groups VALUES(1, 1);
INSERT INTO accounts_groups VALUES(2, 2);
INSERT INTO accounts_groups VALUES(2, 3);
INSERT INTO accounts_groups VALUES(3, 2);
INSERT INTO accounts_groups VALUES(3, 3);
INSERT INTO accounts_groups VALUES(3, 4);
INSERT INTO accounts_groups VALUES(4, 2);
INSERT INTO accounts_groups VALUES(4, 3);
INSERT INTO accounts_groups VALUES(4, 4);
 
 
INSERT INTO groups_roles VALUES(1, 1);
INSERT INTO groups_roles VALUES(1, 2);
INSERT INTO groups_roles VALUES(1, 3);
INSERT INTO groups_roles VALUES(1, 4);
INSERT INTO groups_roles VALUES(1, 5);
INSERT INTO groups_roles VALUES(1, 6);
INSERT INTO groups_roles VALUES(1, 7);
INSERT INTO groups_roles VALUES(1, 8);
INSERT INTO groups_roles VALUES(1, 9);
INSERT INTO groups_roles VALUES(1, 10);
INSERT INTO groups_roles VALUES(1, 11);
INSERT INTO groups_roles VALUES(1, 12);
INSERT INTO groups_roles VALUES(1, 13);
INSERT INTO groups_roles VALUES(2, 7);
INSERT INTO groups_roles VALUES(2, 8);
INSERT INTO groups_roles VALUES(2, 10);
INSERT INTO groups_roles VALUES(2, 12);
INSERT INTO groups_roles VALUES(3, 7);
INSERT INTO groups_roles VALUES(3, 8);
INSERT INTO groups_roles VALUES(3, 10);
INSERT INTO groups_roles VALUES(3, 12);
INSERT INTO groups_roles VALUES(4, 7);
INSERT INTO groups_roles VALUES(4, 8);
INSERT INTO groups_roles VALUES(4, 10);
INSERT INTO groups_roles VALUES(4, 12);
INSERT INTO groups_roles VALUES(4, 13);
 
 
INSERT INTO groups_roles_parameters VALUES(1, 2,  8);
INSERT INTO groups_roles_parameters VALUES(1, 4,  8);
INSERT INTO groups_roles_parameters VALUES(1, 5,  8);
INSERT INTO groups_roles_parameters VALUES(1, 8,  8);
INSERT INTO groups_roles_parameters VALUES(1, 13, 8);
INSERT INTO groups_roles_parameters VALUES(2, 8,  3);
INSERT INTO groups_roles_parameters VALUES(3, 8,  5);
INSERT INTO groups_roles_parameters VALUES(4, 13, 7);
[SQL] pobierz, plaintext