Complex Joins between tables with non-primary keys

6 months ago

Breki
Member | 6
+
0
-

I've got a fairly complex database that I'm working with, where I'm regularly coming up against the problem where I need to join two tables on columns that are _not_ their Primary Key. Here's an example of the kinds of thing that I'm trying to do (but obviously not this dataset):

Table “Cars”
Id = Int
CarSerialNumber = VARCHAR
ModelId = INT (foreign key = Model.Id)
OwnerId = INT (foreign key = Person.Id)

Table “Accidents”
Id = INT
CarSerialNumber = INT (Foreign key = Cars.LicensePlate)
Date = DATETIME
DriverId = INT (Foreign key = Person.Id)
DrivingFrom = INT (Foreign key = Location.Id)
DrivingTo = INT (Foreign key = Location.Id)

Table “Location”
Id = INT
Name = Varchar
CountryId = INT (Foreign Key = Nation.Id)

Table “Person”
Id = INT
Name = VARCHAR
Age = VARCHAR
NationalityId = INT (Foreign key = Nation.Id)

Table “Nation”
Id = INT
Name = VARCHAR

Table “Model”
Id = INT
Name = VARCHAR
Nationality = INT (Foreign key = Nation.Id)

Now, If I wanted to make a query towards this where I wanted, for example, the oldest person's nationality for each combination of available “To” and “From” locations, I could write this in MySQL. But when I try to replicate the same kind of query through Nette Database Selection, I run up against a wall. And I don't want to have to use $database->query(“SELECT FROM …”); because the result is locked in place. I can't store it in the template and then re-use it or filter it at a later stage.

Ideally, what I want is to be able to write something like this in Nette, but I can't figure out a good way to do it:

<?php
$dataset = $database->table('Person')
           ->alias('Person', 'p')
           ->join('Nation', 'Id', '=', 'p.NationalityId')
           ->join('Accidents', 'DriverId', '=', 'p.Id')
           ->join('Location', 'Id', '=', 'Accidents.FromLocationId')
           ->alias('Location', 'fromLoc')
           ->join('Location', 'Id', '=', 'Accidents.ToLocationId')
           ->alias('Location', 'toLoc')
           ->where('toLoc.CountryId', '!=', 'p.NationId')
           ->where('fromLoc.CountryId', '!=', 'p.NationId')
           ->select('p.Name, toLoc.Name, fromLoc.Name')
?>

So basically, using the data mentioned, I'd like to be able to craft a Nette Database query to build a dataset of all people who have had car accidents driving to or from a location that is not their home country. This $dataset could then be called in various presenters using things like:

$this->template->dataInfo = $dataset->where(‘p.NationID = ?’, $id);

or something like that.

Any ideas? I'm running into a wall here, and the documentation for things like joinWhere and so on is _very_ bad. :)

6 months ago

Felix
Member | 743
+
0
-

Hi.

You're right, documentation for joinWhere and other features like that is kinda in bad condition.

I suggest you try to discover our API (https://api.nette.org/…lection.html).

Anywhere, if you have any further questions or troubles with it, don't hesitate to ask me or others.

You can reach me on Gitter https://gitter.im/f3l1x, it might be faster to help you there.

6 months ago

Breki
Member | 6
+
0
-

Thanks for your answer! And yeah, I've been reading the API documentation quite a lot, but I'm not QUITE there yet in understanding more complex joins in Nette Database yet … I'm getting closer! … but not quite there. ;) Here's a question which would help me a lot! How would you express something like this MySQL query using Nette?

SELECT t.Name, from.Name, to.Name, p.Name, t.DateLeft, t.DateArrived, pc.Name, last.DateLeft
FROM Trip AS t
JOIN Country AS from ON from.Id = t.DepartureCountryId
JOIN Country AS to ON to.Id = t.ArrivalCountryId
JOIN Person AS p ON p.Id = t.TravellerPersonId
JOIN Country AS pc ON pc.Id = p.NationalityCountryId
JOIN Trip AS last ON last.Name = p.LastCompletedTrip

Thanks! :)