Best practicies: Reading data from two tables with foreign key

3 years ago

Ajax
Member | 57
+
+1
-

Hello!

I have schema like this:

CREATE TABLE `contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `prefix` varchar(16) COLLATE utf8_czech_ci NOT NULL,
  `name` varchar(64) COLLATE utf8_czech_ci NOT NULL,
  `surname` varchar(64) COLLATE utf8_czech_ci NOT NULL,
  `sufix` varchar(16) COLLATE utf8_czech_ci NOT NULL,
  `street` varchar(64) COLLATE utf8_czech_ci NOT NULL,
    ...
  PRIMARY KEY (`id`)
)

CREATE TABLE `le_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_contacts` int(10) unsigned NOT NULL,
  `company_name` varchar(128) COLLATE utf8_czech_ci NOT NULL,
  `cid` varchar(16) COLLATE utf8_czech_ci NOT NULL,
  `vat` varchar(16) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_contacts` (`id_contacts`),
  CONSTRAINT `le_data_ibfk_1` FOREIGN KEY (`id_contacts`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
);

Now, I need to read data from contacts and append data from le_data table if exists (aka left join). I read in doc, that best practice is read data only from one table without joins. But somehow, I don't know how to read data from both tables, except using not wanted ->select(contacts.*, le_data.*). Nette database returns me only contacts or only le_data, or exceptions No reference found for $contacts->ref(le_data). Can someone explain me what I'm doing wrong?

3 years ago

premek_k
Member | 173
+
0
-

Do you use an INNODB database storage?

3 years ago

Ajax
Member | 57
+
0
-

Sorry, Yes, I do…

3 years ago

premek_k
Member | 173
+
0
-

Maybe this could help: http://public.skrasek.com/…_2012_04_28/#1

(For some strange reason sometimes this presentation fails on #22nd page. To solve this, change the end of url manualy to get to another page)

EDIT: or better – go to the end of presentation and rewind to start. Then all pages are accessible.

Last edited by premek_k (2016-02-02 09:00)

3 years ago

Ajax
Member | 57
+
0
-

OK, thanks. I got somewhere…

$data = $this->database
    ->table(self::TABLE_CONTACTS)
    ->where(':le_data.id_contacts');

foreach ($data as $item) {
    dump($item->id);
    dump($item->contact_name);
    dump($item->related('le_data')->company_name);
}

Problem is, I'm getting error Cannot read an undeclared property Nette\Database\Table\GroupedSelection::$company_name. I uderstand, that grouped selection is ready for 1:N, but I have 1:1. Is it possible to read it somehow without foreach? Thanks…

3 years ago

CZechBoY
Member | 3360
+
0
-

Fetch one row with ->fetch();

3 years ago

Ajax
Member | 57
+
0
-

Ok, that works. Thanks!

It is strange to use table name (in related) and fetch in template. Is it really best practice? In my point of view, someone who creates html design should not use functions like fetch. But maybe I'm wrong… Is there better way?

3 years ago

CZechBoY
Member | 3360
+
0
-

If you need only one row, then fetch it (you can do it in model).

3 years ago

Ajax
Member | 57
+
0
-

If I fetch it in model, result will be same when I force database layer to left join with ->select(contacts.*, le_data.*). is that right?