Nette Database: No reference found for Join query

Question

How do you create a query in the renderDefault function with JOINS? I have been looking at nette docs for a while and I can't seem to find my answer. That's why I wanted to ask someone experienced. Who could already know the answer?

** Let's say you got this query**

SELECT a.id, a.firstname, a.lastname, b.name FROM racers a JOIN types b ON b.id = a.type

And you would like to create it like this:

public function renderDefault(): void
{
$this->template->racers = $this->database->table('racers');
}

One of my ideas was to do something like this

public function renderDefault(): void
{
$this->template->racers = $this->database->table('racers')
->select('racers.id, racers.firstname, racers.lastname, types.name')
->joinWhere('types', 'types.id = racers.type');
}

for some reason "my" query seems to work until I need to use it in foreach loop in a latte. and that is why I am wondering what is a better way to do this.

{foreach $racers as $racer}
<tr>
  <th scope="row">{$racer->id}</th>
  <td>{$racer->firstname}</td>
  <td>{$racer->lastname}</td>
  <td>{$racer->name}</td>
</tr>
{/foreach}

Error:

Nette\InvalidArgumentException 
No reference found for $racers->types.

Answer 1

If you want to use the simpler database explorer API over regular SQL queries through the core API, you will need to set up foreign keys in the database properly.

The explorer API will allow you to work with the table directly, inferring which columns to select and which tables to use automatically:

public function renderDefault(): void {
    $this->template->racers = $this->database->table('racers');
}

<tr n:foreach="$racers as $racer">
  <th scope="row">{$racer->id}</th>
  <td>{$racer->firstname}</td>
  <td>{$racer->lastname}</td>
  <td>{$racer->type->name}</td>
</tr>