Best practicies: Reading data from two tables with foreign key

Ajax

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?

premek_k

Do you use an INNODB database storage?

Ajax

Sorry, Yes, I do…

premek_k

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)

Ajax

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…

CZechBoY

Fetch one row with ->fetch();

Ajax

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?

CZechBoY

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

Ajax

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?