Best practicies: Reading data from two tables with foreign key
- Ajax
- Member | 61
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
- Member | 172
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
- Member | 61
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…
