Problem s Joinováním 3 tabulek

A máš v databázi správně nastavené klíče ?? Pošli přehlednou strukturu tabulek a jak máš nastavené cizí klíče! Dále pošli ukázku kódu pro Nette Explorer, který Ti nefungoval.

Kliče mám nastavene z user_employee na user a z user_role na user …

Struktura tabulek:

CREATE TABLE `user` (
  `user__id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user__password` varchar(255) DEFAULT NULL,
  `user__email` varchar(254) NOT NULL,
  `user__name` varchar(100) DEFAULT NULL,
  `user__surname` varchar(100) DEFAULT NULL,
  `user__active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`user__id`),
  UNIQUE KEY `email` (`user__email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE `user_employee` (
  `user_employee__user_id` int(10) unsigned NOT NULL,
  `user_employee__employee_phone` varchar(100) DEFAULT NULL,
  `user_employee__employee_email` varchar(254) DEFAULT NULL,
  `user_employee__active` tinyint(4) DEFAULT 1,
  PRIMARY KEY (`user_employee__user_id`),
  CONSTRAINT `user_employee_ibfk_1` FOREIGN KEY (`user_employee__user_id`) REFERENCES `user` (`user__id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE `user_role` (
  `user_role__user_id` int(8) unsigned NOT NULL,
  `user_role__role_id` int(2) unsigned NOT NULL,
  `user_role__user_active` int(1) unsigned NOT NULL DEFAULT 1,
  UNIQUE KEY `user_id` (`user_role__user_id`,`user_role__role_id`),
  KEY `user_role_ibfk_02` (`user_role__role_id`),
  CONSTRAINT `user_role_ibfk_01` FOREIGN KEY (`user_role__user_id`) REFERENCES `user` (`user__id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_role_ibfk_02` FOREIGN KEY (`user_role__role_id`) REFERENCES `user_setting_role` (`user_setting_role__id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

a tady jeden ze zkusebnich kodu >> ,

 public function getSelection(): Selection
    {
        $rows = $this->database->table('user_employee')
    			->select('user.*, user_employee.*, IF(user_role.user_role__role_id = 4 AND user_role.user_role__user_active = 1, 1, 				NULL) AS role_check')
    			->where('user_employee.user_employee__active', 1)
    			->joinWhere('user', 'user.user__id = user_employee.user_employee__user_id')
    			->joinWhere('user_role', 'user_role.user_role__user_id = user.user__id AND user_role.user_role__role_id = 4 AND user_role.user_role__user_active = 1', 'LEFT');

    	bdump($rows->fetchall());
    	return $rows;
}

v SQL me dotaz funguje, a vráti mi řádky, které očekávam

@QwetakCZ

Ahoj,

jdeš na to zbytečně komplikovaným SQL způsobem, Explorer nabízí jiné elegantní techniky. Vyzkoušel jsem si to a našel způsob, jak požadovaná data Explorer stylem vytáhnout.

Protože názvy sloupců jak je máš v tabulkách jsou zbytečně komplikované, tak jsem si Tvoje tabulky přepsal srozumitelně, abych se v tom lépe vyznal.

Přepsané tabulky:

-- Adminer 4.8.1 MySQL 8.0.17 dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(25) COLLATE utf8mb4_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;


DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_czech_ci NOT NULL,
  `surname` varchar(30) COLLATE utf8mb4_czech_ci NOT NULL,
  `email` varchar(40) COLLATE utf8mb4_czech_ci NOT NULL,
  `is_employee` tinyint(4) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;


DROP TABLE IF EXISTS `user_employee`;
CREATE TABLE `user_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_czech_ci NOT NULL,
  `email` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_czech_ci NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `user_employee_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;


DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci;


-- 2024-10-20 23:59:57

A teď dotazy v Exploreru.

Modelová třída s metodou pro výběr z tabulky user:

class UserRepository
{
    public function __construct(private Explorer $explorer)
    {}

    public function getUsers(): Selection   // varianta se sloupcem is_employee
    {
        return $this->explorer->table('user')
            ->where('is_employee', true)
            ->where(':user_role.role_id', 1)
            ->where(':user_role.active', true);
    }

    public function getUsers(): Selection       // varianta bez sloupce is_employee
    {
        return $this->explorer->table('user')
            ->where(':user_employee.id NOT', null)
            ->where(':user_role.role_id', 1)
            ->where(':user_role.active', true);
    }

}

Jestli je uživatel zaměstnanec nebo ne je lepší explicitně vyjádřit boolean polem is_employee, je to čitelnější než pouhá existence dat v připojené tabulce a lépe se s tím pracuje, ale filtraci zaměstanců lze provést i bez pole is_employee dotazem ->where(‚:user_employee.id NOT‘, null)

Left join ani nevím zda v Exploreru nějak schůdně funguje, metodu joinWhere() kterou Jsi použil jsem ani v dokumentaci nikde nenašel. Explorer ale umí skvěle dotahovat data z related tabulek a toho využijeme v šabloně:

{block 'content'}
    <div class="box-container product-container">
        <table>
            <tr>
                <td>Uživatel</td>
                <td>Aktivní</td>
                <td>Zaměstnanec</td>
                <td>Email</td>
                <td>Telefon</td>
                <td>Role</td>
            </tr>
            {foreach $users as $usr}
                {varType Nette\Database\Table\ActiveRow $usr}
                {var
                    $employee = $usr->related('user_employee')->fetch(),
                    $userRoles = $usr->related('user_role')->where('user_role.active', true)
                }
                <tr class="body">
                    <td>{$usr->name} {$usr->surname}</td>
                    <td>{$usr->active}</td>
                    <td>{$usr->is_employee}</td>
                    <td>{$employee?->email}</td>
                    <td>{$employee?->phone}</td>
                    <td>
                        {foreach $userRoles as $userRole}
                            {$userRole->role->title}{sep}, {/sep}
                        {/foreach}
                    </td>
                </tr>
            {/foreach}

        </table>
    </div>
{/block}

Otestoval jsem si to a vybírá to správná data podle Tvých požadavků. Jak vidíš v tříde UserRepository jsou vlastní where podmínky velmi jednoduché a na první pohled srozumitelné !! Důležité je pochopit úlohu dvojtečky, ta označuje, že tabulka je ve vztahu k hlavní tabulce related čili podřízená (child) nebo také s vazbou hasMany – na rozdíl od tečky, která označuje referenced tabulky, neboli nadřízené (parent)

Kde Jsi zjistil, jaké parametry předat do metody joinWhere(), protože v dokumentaci tato metoda není popsána a to co je v API dokumentaci je nedostatečné pro použití ??

Metodu joinWhere() jsem zkoušel, ale Explorer po mě chtěl foreign keys obráceným způsobem než jsou v databázi, což ale nejde udělat, to by nefungovalo.

Editoval m.brecher (21. 10. 2:37)

Mockrát děkuji za snahu, bohužel projekt je už ve fázi několika měsíčního vývoje a nedokáži tam změny takto zakomponovat. Tudíž nejjednodušší řešení vyhrálo a to, že do user_employee přidame jak jste navrhoval sloupec, na který se ptáme v tabulce user_role a hodl tato informace bude duplicitni …

Děkuji mnohokrát za pomoc :)