Is it possible to access fields in a joined table

问题:

I have a Persons table and an Events table that are related via an Attends table, such that a record exists the Attends table when I wish to record the fact that a person is due to attend a particular Event in a particular role (e.g. organiser, etc.):

Persons
+----+-------------+
| id | surname     |
+----+-------------+
|  1 | Robinson    |
|  2 | Savage      |
...

Events
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | Half Term Competition |
|  2 | Christmas Competition |
...

attends
+---------+----------+--------+
| eventId | personId | roleId |
+---------+----------+--------+
|       1 |        1 |      1 |
|       1 |        2 |      6 |
...

I want to produce a list of who is going and what they are doing at an event, but I can't find a way of doing it using .join() in Vapor query. It seems that you can filter on the joined table, but not include any of the fields. Am I missing something or is this impossible?

I've tried many variations on the theme of:

Person.join(Attends.self).filter(Attends.self,Event.foreignIdKey == eventId)

I get the correct Person records, but no way of accessing what their 'role' is from the joined Attends record.

If I do a raw query, then it returns a Node representation. I can easily view this using Leaf, but it is not at all obvious how to iterate over it in the way I would like to generate a PDF with images, etc.

回答1:

The Vapor docs for Fluent relations is probably what you are looking for.

From what I can tell, you are using a Many to Many (sibling) relation, so this code might be what you want:

extension Persion {
    var roles: Siblings<Persion, Role, Pivot<Person, Role>> {
        return siblings()
    }
}

Then get the roles:

let roles = person.roles.all()

I might have got this wrong as I can't see your code, but it should give you some pointers.

回答2:

The trick here is to make sure you are taking advantage of joins in the database and not doing individual queries for each relation result - which would not be optimal.

I think this is a fairly new(ly documented) feature: https://docs.vapor.codes/3.0/fluent/querying/#join

This allows the following type of query:

Client.query(on: conn).join(\Client.companyId, to: \Company.id)
    .filter(\.attr1 > 12)
    .filter(\Company.name == "ACME")
    .alsoDecode(Company.self)
    .all()

This code returns a tuple of (Client,Company). I'm not sure how it handles many-to-one relations or whether each duplicate reference is the same class instance.