Complex Joins between tables with non-primary keys

about a month ago

Member | 6

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)
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
NationalityId = INT (Foreign key = Nation.Id)

Table “Nation”
Id = INT

Table “Model”
Id = INT
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:

$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. :)

about a month ago

Member | 673


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

I suggest you try to discover our API (…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, it might be faster to help you there.

about a month ago

Member | 6

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! :)

Board footer

Hostováno na e-infrastruktuře CESNET. Děkujeme za podporu sdružení CESNET, z.s.p.o.