Table joins in Fluent 4

Database fashions

Fluent is a Swift ORM framework written for Vapor. You need to use fashions to signify rows in a desk, migrations to create the construction for the tables and you may outline relations between the fashions utilizing Swift property wrappers. That is fairly a easy manner of representing father or mother, baby or sibling connections. You may “keen load” fashions via these predefined relation properties, which is nice, however typically you do not need to have static varieties for the relationships.

I am engaged on a modular CMS and I am unable to have hardcoded relationship properties contained in the fashions. Why? Nicely, I need to have the ability to load modules at runtime, so if module A relies upon from module B via a relation property then I am unable to compile module A independently. That is why I dropped a lot of the cross-module relations, however I’ve to put in writing joined queries. 😅

Buyer mannequin

On this instance we’re going to mannequin a easy Buyer-Order-Product relation. Our buyer mannequin could have a primary identifier and a reputation. Take into account the next:

last class CustomerModel: Mannequin, Content material 
    static let schema = "prospects"
    
    @ID(key: .id) var id: UUID?
    @Subject(key: "identify") var identify: String

    init()  

    init(id: UUID? = nil, identify: String) 
        self.id = id
        self.identify = identify
    

Nothing particular, only a primary Fluent mannequin.

Order mannequin

Clients could have a one-to-many relationship to the orders. Because of this a buyer can have a number of orders, however an order will all the time have precisely one related buyer.

last class OrderModel: Mannequin, Content material 
    static let schema = "orders"
    
    @ID(key: .id) var id: UUID?
    @Subject(key: "date") var date: Date
    @Subject(key: "customer_id") var customerId: UUID

    init()  

    init(id: UUID? = nil, date: Date, customerId: UUID) 
        self.id = id
        self.date = date
        self.customerId = customerId
    

We may make the most of the @Guardian and @Baby property wrappers, however this time we’re going to retailer a customerId reference as a UUID kind. In a while we’re going to put a international key constraint on this relation to make sure that referenced objects are legitimate identifiers.

Product mannequin

The product mannequin, identical to the shopper mannequin, is completely impartial from the rest. 📦

last class ProductModel: Mannequin, Content material 
    static let schema = "merchandise"
    
    @ID(key: .id) var id: UUID?
    @Subject(key: "identify") var identify: String

    init()  

    init(id: UUID? = nil, identify: String) 
        self.id = id
        self.identify = identify
    

We are able to create a property with a @Sibling wrapper to specific the connection between the orders and the merchandise, or use joins to question the required knowledge. It actually would not matter which manner we go, we nonetheless want a cross desk to retailer the associated product and order identifiers.

OrderProductModel

We are able to describe a many-to-many relation between two tables utilizing a 3rd desk.

last class OrderProductModel: Mannequin, Content material 
    static let schema = "order_products"
    
    @ID(key: .id) var id: UUID?
    @Subject(key: "order_id") var orderId: UUID
    @Subject(key: "product_id") var productId: UUID
    @Subject(key: "amount") var amount: Int

    init()  

    init(id: UUID? = nil, orderId: UUID, productId: UUID, amount: Int) 
        self.id = id
        self.orderId = orderId
        self.productId = productId
        self.amount = amount
    

As you may see we will retailer additional information on the cross desk, in our case we’re going to affiliate portions to the merchandise on this relation proper subsequent to the product identifier.

Migrations

Fortuitously, Fluent provides us a easy method to create the schema for the database tables.

struct InitialMigration: Migration 

    func put together(on db: Database) -> EventLoopFuture<Void> 
        db.eventLoop.flatten([
            db.schema(CustomerModel.schema)
                .id()
                .field("name", .string, .required)
                .create(),
            db.schema(OrderModel.schema)
                .id()
                .field("date", .date, .required)
                .field("customer_id", .uuid, .required)
                .foreignKey("customer_id", references: CustomerModel.schema, .id, onDelete: .cascade)
                .create(),
            db.schema(ProductModel.schema)
                .id()
                .field("name", .string, .required)
                .create(),
            db.schema(OrderProductModel.schema)
                .id()
                .field("order_id", .uuid, .required)
                .foreignKey("order_id", references: OrderModel.schema, .id, onDelete: .cascade)
                .field("product_id", .uuid, .required)
                .foreignKey("product_id", references: ProductModel.schema, .id, onDelete: .cascade)
                .field("quantity", .int, .required)
                .unique(on: "order_id", "product_id")
                .create(),
        ])
    

    func revert(on db: Database) -> EventLoopFuture<Void> 
        db.eventLoop.flatten([
            db.schema(OrderProductModel.schema).delete(),
            db.schema(CustomerModel.schema).delete(),
            db.schema(OrderModel.schema).delete(),
            db.schema(ProductModel.schema).delete(),
        ])
    

If you wish to keep away from invalid knowledge within the tables, you need to all the time use the international key and distinctive constraints. A international key can be utilized to verify if the referenced identifier exists within the associated desk and the distinctive constraint will be sure that just one row can exists from a given subject.

Becoming a member of database tables utilizing Fluent 4

We have now to run the InitialMigration script earlier than we begin utilizing the database. This may be achieved by passing a command argument to the backend utility or we will obtain the identical factor by calling the autoMigrate() technique on the applying occasion.

For the sake of simplicity I’ll use the wait technique as a substitute of async Futures & Guarantees, that is effective for demo functions, however in a real-world server utility you need to by no means block the present occasion loop with the wait technique.

That is one attainable setup of our dummy database utilizing an SQLite storage, however in fact you need to use PostgreSQL, MySQL and even MariaDB via the accessible Fluent SQL drivers. 🚙

public func configure(_ app: Utility) throws 

    app.databases.use(.sqlite(.file("db.sqlite")), as: .sqlite)

    app.migrations.add(InitialMigration())

    strive app.autoMigrate().wait()

    let prospects = [
        CustomerModel(name: "Bender"),
        CustomerModel(name: "Fry"),
        CustomerModel(name: "Leela"),
        CustomerModel(name: "Hermes"),
        CustomerModel(name: "Zoidberg"),
    ]
    strive prospects.create(on: app.db).wait()
    
    let merchandise = [
        ProductModel(name: "Hamburger"),
        ProductModel(name: "Fish"),
        ProductModel(name: "Pizza"),
        ProductModel(name: "Beer"),
    ]
    strive merchandise.create(on: app.db).wait()

    
    let order = OrderModel(date: Date(), customerId: prospects[0].id!)
    strive order.create(on: app.db).wait()

    let beerProduct = OrderProductModel(orderId: order.id!, productId: merchandise[3].id!, amount: 6)
    strive beerProduct.create(on: app.db).wait()
    let pizzaProduct = OrderProductModel(orderId: order.id!, productId: merchandise[2].id!, amount: 1)
    strive pizzaProduct.create(on: app.db).wait()

We have now created 5 prospects (Bender, Fry, Leela, Hermes, Zoidberg), 4 merchandise (Hamburger, Fish, Pizza, Beer) and one new order for Bender containing 2 merchandise (6 beers and 1 pizza). 🤖

Interior be a part of utilizing one-to-many relations

Now the query is: how can we get the shopper knowledge primarily based on the order?

let orders = strive OrderModel
    .question(on: app.db)
    .be a part of(CustomerModel.self, on: OrderModel.$customerId == CustomerModel.$id, technique: .inside)
    .all()
    .wait()

for order in orders 
    let buyer = strive order.joined(CustomerModel.self)
    print(buyer.identify)
    print(order.date)

The reply is fairly easy. We are able to use an inside be a part of to fetch the shopper mannequin via the order.customerId and buyer.id relation. After we iterate via the fashions we will ask for the associated mannequin utilizing the joined technique.

Joins and plenty of to many relations

Having a buyer is nice, however how can I fetch the related merchandise for the order? We are able to begin the question with the OrderProductModel and use a be a part of utilizing the ProductModel plus we will filter by the order id utilizing the present order.

for order in orders 
    

    let orderProducts = strive OrderProductModel
        .question(on: app.db)
        .be a part of(ProductModel.self, on: OrderProductModel.$productId == ProductModel.$id, technique: .inside)
        .filter(.$orderId == order.id!)
        .all()
        .wait()

    for orderProduct in orderProducts 
        let product = strive orderProduct.joined(ProductModel.self)
        print(product.identify)
        print(orderProduct.amount)
    

We are able to request the joined mannequin the identical manner as we did it for the shopper. Once more, the very first parameter is the mannequin illustration of the joined desk, subsequent you outline the relation between the tables utilizing the referenced identifiers. As a final parameter you may specify the kind of the be a part of.

Interior be a part of vs left be a part of

There’s a nice SQL tutorial about joins on w3schools.com, I extremely suggest studying it. The primary distinction between an inside be a part of and a left be a part of is that an inside be a part of solely returns these data which have matching identifiers in each tables, however a left be a part of will return all of the data from the bottom (left) desk even when there are not any matches within the joined (proper) desk.

There are lots of various kinds of SQL joins, however inside and left be a part of are the most typical ones. If you wish to know extra concerning the different varieties you need to learn the linked article. 👍

Abstract

Desk joins are actually useful, however it’s important to watch out with them. It’s best to all the time use correct international key and distinctive constraints. Additionally think about using indexes on some rows once you work with joins, as a result of it may well enhance the efficiency of your queries. Velocity may be an vital issue, so by no means load extra knowledge from the database than you really want.

There is a matter on GitHub concerning the Fluent 4 API, and another one about querying particular fields utilizing the .subject technique. Lengthy story quick, joins may be nice and we want higher docs. 🙉