Jak správně získat data z jiné tabulky

jAkErCZ
Člen | 321
+
0
-

Čau,
Mám dotaz mám tabulku kde si vrací data dle uživatelů a mám tabulku která má v sobě aktualizace a rád bych vrátil jen ty které patří k těm projektům.
Napadlo mě něco takového ale přijde mi to že zbytečně zatěžuji db jak tohle můžu udělat jednodušeji?

$projects = $this->database->table(self::TABLE_NAME)->where(self::COLUMN_ID_USER, $user_id);
                foreach ($projects as $project){
                    $notifications[] = $this->database->table(self::TABLE_UPDATE_STATUS)->where(self::COLUMN_ID, $project->project_id)->where('postoped_date LIKE ?','%' . $date->format('Y-m-d') . '%')->fetchAll();
                }

Abych dle vrácených dat uživatele to vrátilo aktualizace k těm datům.

:)

DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `fenix_project_id` varchar(100) COLLATE utf8_czech_ci DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('10','20','25','30','35','40','50','60','70','80','100','101','102','0','duplicity','postoped') COLLATE utf8_czech_ci NOT NULL DEFAULT '10',
  `description` text COLLATE utf8_czech_ci NOT NULL,
  `client_id` int(11) NOT NULL,
  `source_id` int(11) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`project_id`),
  KEY `source_id` (`source_id`),
  KEY `user_id` (`user_id`),
  KEY `client_id` (`client_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `projects_ibfk_2` FOREIGN KEY (`source_id`) REFERENCES `source` (`source_id`) ON DELETE NO ACTION,
  CONSTRAINT `projects_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `projects_ibfk_5` FOREIGN KEY (`client_id`) REFERENCES `client` (`client_id`) ON DELETE NO ACTION,
  CONSTRAINT `projects_ibfk_6` FOREIGN KEY (`group_id`) REFERENCES `project_group` (`group_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


DROP TABLE IF EXISTS `status_update`;
CREATE TABLE `status_update` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `date` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `status` enum('10','20','25','30','35','40','50','60','70','80','100','101','102','0','duplicity','postoped') COLLATE utf8_czech_ci DEFAULT NULL,
  `reason` int(11) DEFAULT NULL,
  `message` text COLLATE utf8_czech_ci DEFAULT NULL,
  `postoped_date` date DEFAULT NULL,
  PRIMARY KEY (`status_id`),
  KEY `project_id` (`project_id`),
  CONSTRAINT `status_update_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Díky za rady :)

David Matějka
Moderator | 6445
+
+1
-

použij related