Einer oder doch zwei Querys?

**TobiaZ** · 27.12.2009, 20:16

OffTopic:
den Ansatz solltest du ja eigentlich liefern - und wir machen dann die Anmerkungen.

Ich glaube ich hätte prinzipiell das Menü anders strukturiert, aber letztendlich sollte es doch möglich sein, die query so zu aufzubauen, dass zu den menüeinträgen (menu_rechte) die rechte des jeweiligen users gejoint werden. (die weiteren joins in den menütabellen dienen dann ja nur noch der optik.

Probiers einfach mal.

**medium22** · 03.01.2010, 14:24

Hallo zusammen

Zuerst mal Frohes Neues!

Hab nun mal wieder Zeit gefunden daran zu werkeln und basteln. Ansich funktionierte das zu Beginn gar nicht mal so schlecht.

Setze ich folgenden Query ab, erhalte ich das gewünschte Ergebnis für die jeweiligen Rechte des abgefragten Users

Code:

SELECT 
  User_Rechte_Namen.User_Rechte_Namen_Id, User_Rechte_Namen.User_Rechte_Namen_Recht 
FROM 
  User, User_Rechte_Namen, User_Rechte_nm
WHERE 
  User.User_Id = User_Rechte_nm.User_Rechte_nm_userid
AND
  User_Rechte_Namen.User_Rechte_Namen_Id = User_Rechte_nm.User_Rechte_nm_rechteid 
AND 
  User.User_Id = '2'

(die letzte Bedingung ist hier von Hand auf '2' gesetzt, würde im Skript selbst dann aus der Session kommen)
Das ganze ist sicherlich besser mit einem JOIN zu machen, ist mir aber so nicht gelungen, bekam immer hunderte von gleichen Results.

Diese Ergebnisse gilt es nun (wenn möglich im selben Query) auf das Menu anzuwenden. Also die Rechte des Users (die Rechteid) auf die n:m-Tabelle des Menus anzuwenden.
Wie das jedoch gehen soll ist mir bisher grad' noch ein Rätsel.

Vorschläge, Ideen, Anregungen?

**AmicaNoctis** · 03.01.2010, 14:33

Hallo,

ohne Kenntnis der Primär- und Fremdschlüssel der beteiligten Tabellen können wir dir bei den Joins nicht weiterhelfen.

Gruß,

Amica

**medium22** · 03.01.2010, 15:17

Die kann ich liefern, kein Ding. :-)

Code:

--
-- Tabellenstruktur für Tabelle `User`
--

CREATE TABLE IF NOT EXISTS `User` (
  `User_Id` int(11) NOT NULL auto_increment,
  `User_Name` varchar(30) character set latin1 collate latin1_german1_ci NOT NULL,
  `User_Pass` varchar(50) character set latin1 collate latin1_german1_ci NOT NULL
  PRIMARY KEY  (`User_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=399 ;

Code:

--
-- Tabellenstruktur für Tabelle `User_Rechte_Namen`
--

CREATE TABLE IF NOT EXISTS `User_Rechte_Namen` (
  `User_Rechte_Namen_Id` int(4) NOT NULL auto_increment,
  `User_Rechte_Namen_Recht` text NOT NULL,
  `User_Rechte_Namen_Titel` text NOT NULL,
  `User_Rechte_Namen_Team` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`User_Rechte_Namen_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=148 ;

--
-- Daten für Tabelle `User_Rechte_Namen`
-- User_Rechte_Namen_Recht ist der Name des Rechts für das System
-- User_Rechte_Namen_Titel ist der Name für die Ausgabe
--

INSERT INTO `User_Rechte_Namen` 
  (`User_Rechte_Namen_Id`, `User_Rechte_Namen_Recht`, 
  `User_Rechte_Namen_Titel`, `User_Rechte_Namen_Team`) 
VALUES
(29, 'User_Rechte_Links_Startseite_2', 'Link: Startseite', 2),
(32, 'User_Rechte_Links_Ausloggen_2', 'Link: Exit', 2),
(35, 'User_Rechte_Links_NeuerEintrag_2', 'Link: Neuer Eintrag', 2),
(38, 'User_Rechte_Links_EinträgeAnsehen_2', 'Link: Einträge lesen', 2),
(45, 'User_Rechte_Links_Suche_2', 'Link: Suche', 2);

Code:

--
-- Tabellenstruktur für Tabelle `User_Rechte_nm`
--

CREATE TABLE IF NOT EXISTS `User_Rechte_nm` (
  `User_Rechte_nm_id` int(10) NOT NULL auto_increment,
  `User_Rechte_nm_userid` int(5) NOT NULL,
  `User_Rechte_nm_rechteid` int(5) NOT NULL,
  PRIMARY KEY  (`User_Rechte_nm_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

--
-- Daten für Tabelle `User_Rechte_nm`
--

INSERT INTO `User_Rechte_nm` 
  (`User_Rechte_nm_id`, `User_Rechte_nm_userid`, `User_Rechte_nm_rechteid`) 
VALUES
(8, 2, 29),
(9, 2, 32),
(10, 2, 35),
(11, 2, 38),
(16, 2, 45);

Die obigen 3 Tabellen sind jene die in meinem letzten Posting abgefragt wurden
und die folgenden 3 Tabellen für das Menu..

Code:

--
-- Tabellenstruktur für Tabelle `Menu`
--

CREATE TABLE IF NOT EXISTS `Menu` (
  `Menu_Id` int(5) NOT NULL auto_increment,
  `Menu_Name` text NOT NULL,
  `Menu_Ziel` text NOT NULL,
  `Menu_Team_Id` int(5) NOT NULL,
  `Menu_Bereich_Id` int(5) NOT NULL,
  PRIMARY KEY  (`Menu_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

--
-- Daten für Tabelle `Menu`
--

INSERT INTO `Menu` 
  (`Menu_Id`, `Menu_Name`, `Menu_Ziel`, `Menu_Team_Id`, `Menu_Bereich_Id`) 
VALUES
(8, 'Startseite', 'intern.php', 2, 10),
(9, 'Exit', 'logout.php', 2, 10),
(10, 'Neuer Eintrag', '#', 2, 10),
(11, 'Einträge lesen', '#', 2, 10);

Code:

--
-- Tabellenstruktur für Tabelle `Menu_Bereiche (im eingangsposting menu_header genannt)`
--

CREATE TABLE IF NOT EXISTS `Menu_Bereiche` (
  `MenuBereiche_Id` int(5) NOT NULL auto_increment,
  `MenuBereiche_Name` text NOT NULL,
  PRIMARY KEY  (`MenuBereiche_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Daten für Tabelle `Menu_Bereiche`
--

INSERT INTO `Menu_Bereiche` (`MenuBereiche_Id`, `MenuBereiche_Name`) VALUES
(10, 'Verschiedenes', '#');

Code:

--
-- Tabellenstruktur für Tabelle `Menu_Rechte`
--

CREATE TABLE IF NOT EXISTS `Menu_Rechte` (
  `Menu_Rechte_id` int(5) NOT NULL auto_increment,
  `Menu_Rechte_rechteid` int(5) NOT NULL,
  `Menu_Rechte_menuid` int(5) NOT NULL,
  PRIMARY KEY  (`Menu_Rechte_id`),
  KEY `Menu_Rechte_id` (`Menu_Rechte_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Daten für Tabelle `Menu_Rechte (n:m)`
--

INSERT INTO `Menu_Rechte` (`Menu_Rechte_id`, `Menu_Rechte_rechteid`, `Menu_Rechte_menuid`) 
VALUES
(1, 29, 8),
(2, 32, 9),
(3, 35, 10),
(4, 38, 11),
(8, 45, 15);

**AmicaNoctis** · 03.01.2010, 15:51

Ok, ein paar Dinge, die mir aufgefallen sind:

Jeder Spaltenname enthält den kompletten Tabellennamen. Ich finde das unübersichtlich, auch wenn es nicht falsch ist. Gibt es einen bestimmten Grund, warum du das machst?

Du benutzt MyISAM statt InnoDB. Dadurch hast du keine Fremdschlüsselconstraints, die aber extrem hilfreich sind, um die Kohärenz und die Integrität der Daten zu erhalten.

Selbst abgesehen von Constraints sind die Fremdschlüsselspalten bei dir nicht indiziert, was Abfragen (insbesondere gejointe) unnötig verlangsamt.

Hast du dir mal ein ERD gemacht, um selbst auch den Überblick zu behalten? Beim Erstellen von Abfragen ist das extrem hilfreich, weil du alles auf einen Blick siehst und weißt, was du mit wem worüber joinen kannst.

Zu guter Letzt: deine Theta-Style-Joins solltest du komplett vergessen. Sieh dir im MySQL-Handbuch die JOIN-Syntax mit ON an und benutze diese. Die Where-Klausel sollte ausschließlich dazu verwendet werden, um die Ergebnisrealation zu filtern und nicht, um einem riesigen Cross-Join (select * from a, b ...) nachträglich noch ein paar Constraints unterzumogeln.

**medium22** · 03.01.2010, 19:41

Hallo,

Jeder Spaltenname enthält den kompletten Tabellennamen. Ich finde das unübersichtlich, auch wenn es nicht falsch ist. Gibt es einen bestimmten Grund, warum du das machst?

Ja, den gibt es. Es kann durchaus vorkommen, dass in einem Query Zeilen mit selbiger Benennung ausgelesen werden, ebenso ist es für mich beim schreiben so übersichtlicher zu wissen was ich grade wovon holen möchte. Sozusagen der Bezug in sich selbst.

Du benutzt MyISAM statt InnoDB. Dadurch hast du keine Fremdschlüsselconstraints, die aber extrem hilfreich sind, um die Kohärenz und die Integrität der Daten zu erhalten.

Würde ich ohne zögern wenn ich könnte - dann könnte ich mir diesen ganze ausgelagerte Beziehungsquark sparen. Aber leider bietet mein Hoster keine InnoDB an.

Hast du dir mal ein ERD gemacht, um selbst auch den Überblick zu behalten? Beim Erstellen von Abfragen ist das extrem hilfreich, weil du alles auf einen Blick siehst und weißt, was du mit wem worüber joinen kannst.

Nein - jedenfalls nicht direkt. Ich habe mir nur mal eine Textdatei angelegt wo alles und jede Tabelle drin steht. Aber ich hab auch ohne diese den Durchblick, arbeite ja nicht an einem Dutzend Projekte sondern nur an diesem einen *g* Gibt es dafür denn eine gute/kostenlose Software mit der man das anstellen kann?

Ja, das mit den JOINs schrieb ich ja bereits. Da bastle ich noch ein wenig - gelang mir bisher nicht wirklich.

**AmicaNoctis** · 03.01.2010, 20:24

Zitat von medium22 Beitrag anzeigen

Ja, den gibt es. Es kann durchaus vorkommen, dass in einem Query Zeilen mit selbiger Benennung ausgelesen werden, ebenso ist es für mich beim schreiben so übersichtlicher zu wissen was ich grade wovon holen möchte. Sozusagen der Bezug in sich selbst.

Ok, aber das kann man aber auch erreichen, wenn man folgende Notation verwendet:

Code:

select
    `t1`.`id` as `product_id`,
    `t1`.`name` as `product_name`,
    `t1`.`is_active`,
    `t2`.`id` as `group_id`,
    `t2`.`name` as `group_name`,
    `t2`.`description`
from `product` as `t1`
left join `group` as `t2` on `t2`.`id` = `t1`.`table2_fkey`

Zitat von medium22 Beitrag anzeigen

Würde ich ohne zögern wenn ich könnte - dann könnte ich mir diesen ganze ausgelagerte Beziehungsquark sparen. Aber leider bietet mein Hoster keine InnoDB an.

Achso, hatte ich überlesen, dann solltest du aber trotzdem die "Fremdschlüssel"-Spalten indizieren.

Zitat von medium22 Beitrag anzeigen

Gibt es dafür denn eine gute/kostenlose Software mit der man das anstellen kann?

Ich mach das mit StarUML, aber es gibt sicher noch besseres. StarUML hatte für mich den Vorteil, dass man es gut über COM scripten kann und ich daher ein Tool bauen konnte, was mir aus einer vorhandenen (InnoDB)-Datenbank die UML-ERDs generiert.

Dass du dein Projekt im Moment gut kennst, will ich gar nicht bezweifeln, aber:
Wird das auch in 2 Jahren noch so sein?
Kannst du das bei fremden Leuten in einem Forum voraussetzen?

Ich hätte es hilfreich gefunden.

Ich weiß daher zwar nicht, wie Menürechte mit den Benutzerrechten zusammengebracht werden sollen, aber gut geraten und ohne konkrete Spaltenauswahl, könnte die Abfrage so aussehen:

Code:

select
	* -- do it yourself ;)
from `User_Rechte_nm` as `uxr`
join `User` as `u` on `u`.`User_Id` = `uxr`.`User_Rechte_nm_userid`
join `User_Rechte_Namen` as `r` on `r`.`User_Rechte_Namen_Id` = `uxr`.`User_Rechte_nm_rechteid`
join `Menu_Rechte` as `mxr` on `mxr`.`Menu_Rechte_rechteid` = `r`.`User_Rechte_Namen_Id`
join `Menu` as `m` on `m`.`Menu_Id` = `mxr`.`Menu_Rechte_menuid`
join `Menu_Bereiche` as `c` on `c`.`MenuBereiche_Id` = `m`.`Menu_Bereich_Id`

Deine langen Namen machen mich wahnsinnig, vor allem, weil sie überall anders heißen -- mal groß, mal klein, mal mit Unterstrich, mal ohne

Amica

**medium22** · 03.01.2010, 21:50

Einen leckeren Cappu gegen den Wahnsinn!

Hab nun verschiedene Programme gefunden in die ich mich die Tage reinfinden muss, aber das wird schon werden.

Du bist im Raten übrigens sehr gut! Dein Query traf genau das worauf ich hinaus wollte - klasse!

Ein paar kleine Änderungen und die Ausgabe bringt genau das was ich möchte

Code:

select
	c.MenuBereiche_Name, c.MenuBereiche_Ziel, 
        m.Menu_Name, m.Menu_Ziel 
        
from `User_Rechte_nm` as `uxr`
join `User` as `u` on `u`.`User_Id` = `uxr`.`User_Rechte_nm_userid`
join `User_Rechte_Namen` as `r` on `r`.`User_Rechte_Namen_Id` = `uxr`.`User_Rechte_nm_rechteid`
join `Menu_Rechte` as `mxr` on `mxr`.`Menu_Rechte_rechteid` = `r`.`User_Rechte_Namen_Id`
join `Menu` as `m` on `m`.`Menu_Id` = `mxr`.`Menu_Rechte_menuid`
join `Menu_Bereiche` as `c` on `c`.`MenuBereiche_Id` = `m`.`Menu_Bereich_Id`
where `u`.`User_Id` = '2'
order by c.MenuBereiche_Name

Damit kann ich das Menu passend zu den Nutzerrechten ausgeben lassen. Das wiederum (damit sich der Bereichsname nicht wiederholt) in einem typischen Gruppenwechsel. Ginge sicherlich auch eleganter, denke ich. *g*

Was meinst Du mit "Fremdschlüssel"-Spalten indizieren? Wie macht man sowas bzw. wo gibts Deutsche Literatur darüber?

**AmicaNoctis** · 03.01.2010, 23:50

Bevor ich jetzt selbst ewig für dich nach Literatur suche...

Code:

create table `blabla` (
    `id` int unsigned not null auto_increment,
    `name` varchar(255) not null default '',
    `group_fkey` int unsigned default null,
    primary key (`id`),
    key (`group_fkey`)
);

Das "key (`group_fkey`)" erzeugt den Index auf die Spalte group_fkey. Im PHPMyAdmin erreichst du das, wenn du bei der Spalte auf den gelben Blitz klickst.

**medium22** · 04.01.2010, 17:50

Hallo,

Danke für den Hinweis. In einem anderen aktuellen Thread wird diese optimierung zZ. ja auch besprochen, der hat mir eben auch schon weitergeholfen bei der Umsetzung bzw. bei dem worauf man speziell zu achten hat.

Nun habe ich ein paar Indizes gesetzt gesetzt. An einigen Orten jammerte phpma dann rum, von wegen zwei Indizes auf der selben Spalte - also löschte ich dort jeweils den Primärschlüssel und änderte diesen auf einen Index.

Führe ich nun folgendes aus..

Code:

 EXPLAIN  SELECT c.MenuBereiche_Name, c.MenuBereiche_Ziel, m.Menu_Name, m.Menu_Ziel
FROM `User_Rechte_nm` AS `uxr`
JOIN `User` AS `u` ON `u`.`User_Id` = `uxr`.`User_Rechte_nm_userid`
JOIN `User_Rechte_Namen` AS `r` ON `r`.`User_Rechte_Namen_Id` = `uxr`.`User_Rechte_nm_rechteid`
JOIN `Menu_Rechte` AS `mxr` ON `mxr`.`Menu_Rechte_rechteid` = `r`.`User_Rechte_Namen_Id`
JOIN `Menu` AS `m` ON `m`.`Menu_Id` = `mxr`.`Menu_Rechte_menuid`
JOIN `Menu_Bereiche` AS `c` ON `c`.`MenuBereiche_Id` = `m`.`Menu_Bereich_Id`
WHERE `u`.`User_Id` = '2'

.. wird mir...

Code:

+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
|select_type|table|type| possible_keys           |          key            |key_len|   ref                    |rows|Extra      |
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
| SIMPLE    |  u  |ref | User_Id                 |        User_Id          |   4   |  const                   | 1  |Using index|
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
| SIMPLE    | mxr |ALL | Menu_Rechte_rechteid,   |          NULL           |  NULL |  NULL                    | 9  |           |
|           |     |    | Menu_Rechte_menuid      |                         |       |                          |    |           |
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
| SIMPLE    | uxr |ref | User_Rechte_nm_userid,  | User_Rechte_nm_rechteid |   4   | .mxr.Menu_Rechte_rechteid| 2  |Using where|
|           |     |    | User_Rechte_nm_rechteid |                         |       |                          |    |           |
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
| SIMPLE    |  r  |ref | User_Rechte_Namen_Id    | User_Rechte_Namen_Id    |   4   | .mxr.Menu_Rechte_rechteid| 12 |Using index|
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
| SIMPLE    |  m  |ref | Menu_Id,Menu_Bereich_Id | Menu_Id                 |   4   | .mxr.Menu_Rechte_menuid  | 10 |           |
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+
| SIMPLE    |  c  |ALL | MenuBereiche_Id         |           NULL          |  NULL |  NULL                    | 9  |Using where|
+-----------+-----+----+-------------------------+-------------------------+-------+--------------------------+----+-----------+

ausgegeben.. Da sind noch immer ein paar "NULL" drin aber irgendwie wollen die nicht weg. Kann es daran liegen das ich den Primärschlüssel gekillt habe? btw. ist dieser Hinweis/Fehlermeldung in phpma so tragisch wenn man Primärschlüssel UND Index auf derselben Spalte hat? (Wenn ja, warum?)

Wenn ich nun das im letzten Beitrag genannte ORDER BY noch hinzufüge (bei diesem Beispiel weggelassen) ändert sich die obige Ausgabe in der ersten Zeile beim Wert 'Extra' auf "Using index; Using temporary; Using filesort", was lt. dem anderen Thread ja gaaaaar nicht gut ist. Wird das allein durch das ORDER BY ausgelöst, bzw. wie kommt das? Einen Index (o.ä.) kann ich auf dieses Feld nicht setzen weil es ein Text-Feld ist.

Hinweis: Hab die Ausgabe schon so gut als möglich gekürzt/gequetscht (erste Spalte "ID" entfernt, ..), noch mehr und die würde Ansicht/Übersicht würde drunter leiden.

**AmicaNoctis** · 04.01.2010, 17:56

Ein Primärschlüssel ist schon ein Index, aber ein besonderer. Aus einer Primärschlüsselspalte eine normale Indexspalte zu machen (also den Primärschlüssel zu zerstören) macht keinen Sinn. Das solltest du rückgängig machen.

Index/Key: die Spalte ist indiziert
Unique Index/Key: die Spalte ist indiziert und muss zusätzlich entweder einen eindeutigen Werte enthalten oder null sein
Primary Key: wie Unique Key, aber null ist nicht erlaubt.

Daran siehst du, dass der PK schon etwas besseres ist als ein normaler Index.

Amica

**medium22** · 04.01.2010, 18:38

Schau einer an..
Die Primary Key's wieder dabei, Tabellen überprüft/optimiert und schon sieht das Resultat ganz anders aus. Nun sind alle typen, schlüssel, referenzen, etc. zumindest nicht mehr NULL und lt. Handbuch so auch nicht falsch.
Lediglich das ".. Using temporary; Using filesort" ist noch vorhanden.

**AmicaNoctis** · 04.01.2010, 18:41

Using filesort? Wo?

**medium22** · 04.01.2010, 18:53

In der ersten Zeile

Code:

+-----------+-----+-----+-----------------+---------+-------+----------------------------+
|select_type|table|type | possible_keys   |   key   |key_len| ref  |rows|Extra           |
+-----------+-----+-----+-----------------+---------+-------+----------------------------+
| SIMPLE    |  u  |const| PRIMARY,User_Id | PRIMARY |   4   | const| 1  |Using index;    |
|           |     |     |                 |         |       |      |    |Using temporary;|
|           |     |     |                 |         |       |      |    |Using filesort  |
+-----------+-----+-----+-----------------+---------+-------+----------------------------+

Einer oder doch zwei Querys?