[MySQL] Wyciąganie danych z tabel many-to-many Opcje

[parzol]

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `roles_users` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `role_id` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY  (`user_id`,`role_id`),
  KEY `fk_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` varchar(127) NOT NULL,
  `username` varchar(32) NOT NULL DEFAULT '',
  `password` varchar(64) NOT NULL,
  `logins` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `last_login` int(10) UNSIGNED,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  UNIQUE KEY `uniq_email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

Jak wyciągnąć możliwie w jednym zapytanie listę użytkowników wraz ze wszystkimi ich rolami?

[nospor]

select * from users
left join roles_users ...
left join roles ....

[viking]

A po co ci w zasadzie tabela roles_users? ja bym dał od razu role_id w users.

[nospor]

@viking pewnie cie zaskocze, ale czasami dobrze jest, by user mogl nalezec do kilku rol a nie tylko jednej

[viking]

Zgadza się, ale w większości przypadków wystarcza dobrze rozplanowana ACLka z dziedziczeniem ról (którego to w tabeli nie widać). Ale to tylko strzelam bo może faktycznie takie są wymagania jak mówisz.

[nospor]

Nie wiem jakie parzol ma wymagania. Wiem natomiast ze uzywa Kohana i struktura tabel jaka ma zostala narzucona przez modul Auth

[parzol]

Nie wiem jakie parzol ma wymagania. Wiem natomiast ze uzywa Kohana i struktura tabel jaka ma zostala narzucona przez modul Auth

Dokładnie. Tabele zostały narzucone i nie chcę tego zmieniać. Nie dyskutowałbym na temat zasadności.

Aktualnie moje zapytanie wygląda więc tak:

[PHP] pobierz, plaintext 
	$sql = DB::select()
			->from($this->_table_name)
			->join('roles_users')->on('users.id', '=', 'roles_users.user_id')
			->join('roles')->on('roles_users.role_id', '=', 'roles.id')
			->execute();
[PHP] pobierz, plaintext 

Jak z tego mogę wyciągnąć Role?

[phpion]

W zasadzie zapytanie, które zbudowałeś, wyciąga to co oczekujesz, tj. wszystkich użytkowników wraz z ich rolami. Dane zwraca w postaci:

Kod

użytkownik1 | rola1
użytkownik1 | rola2
użytkownik1 | rola3
użytkownik2 | rola1

a zapewne oczekiwałbyś np. tak:

Kod

użytkownik1 | rola1, rola2, rola3
użytkownik2 | rola1

Musisz zatem zastosować GROUP_CONCAT oczywiście w połączeniu z GROUP_BY. W Kohanie ogarniesz to w ten sposób.

I na koniec 2 rady:
1. Jawnie podawaj kolumny, które Cię interesują. Twoje zapytanie zwróci przykładowo wartość pod indeksem id. Będzie to identyfikator rekordu. Pytanie tylko z której tabeli: users czy może roles?
2. Pomyśl nad rodzajem złączenia tabel. W przypadku gdy użytkownik nie będzie miał przypisanej żadnej roli nie otrzymasz go w wyniku swojego zapytania. Może aplikacja nie dopuszcza takiej możliwości, ale sama baza już tak więc trzeba to odpowiednio obsłużyć.

[parzol]

phpion - jak zwykle rządzisz!