CODEWITHSUNDEEP
vaporvapor-fluent
Chocoford
Chocoford

Reputation: 321

How to select all columns from a join table in vapor 4?

It seems that vapor add a new feature eagerLoad and remove alsoDecode. It is convenient for those which possessing parent-child or sibling relationships. But not for those without relationship.

I want to implement a tree structure whose nodes cannot be (or I don't know how to) involved in a relationship. Nodes have a parent and many children which are nodes too.

So I have three tables for this structure.

Tree:

| Field       | Type            |  
| ----------- | --------------- |  
| id          | UUID?           |  
| name        | String          |  
| nodes       | [Node]          |
| paths       | [Path]          |

Nodes:
| Field         | Type                       |  
| ------------- | -------------------------- |  
| id            | UUID?                      |  
| type          | NodeType(root, leaf, node) |    
| tree          | Tree                       |

Path:
| Field        | Type      |  
| ------------ | --------- |  
| id           | UUID?     |  
| distance     | Int       |  
| ancestorID   | UUID      |  
| descendantID | UUID      |  
| tree         | Tree      |

The question is if I want to do

SELECT Nodes.id, Nodes.type, Path.ancestorID from Nodes
INNER JOIN Path
ON Nodes.id = Path.descendantID

How to write the codes.

Upvotes: 1

Views: 406

Answers (1)

pikzelz
pikzelz

Reputation: 56

You could also choose to cast to SQLDatabase. Make sure to import SQLKit too, a Fluent database can always be casted as SQLDatabase.

For example: let sqlDb = req.db as? SQLDatabase would give you the power to use custom queries like: sqlDb?.select().from("Nodes").join("Path", on: "Nodes.id = Path.descendantId").all()

For more info on SQLKit see: https://github.com/vapor/sql-kit

Reference: https://docs.vapor.codes/4.0/fluent/advanced/ (Any Fluent Database can be cast to a SQLDatabase. This includes req.db, app.db, the database passed to Migration, etc.)

Upvotes: 2

Related Questions