Home iOS & Swift Books Server-Side Swift with Vapor

30
Advanced Fluent Written by Tim Condon

Note: This update is an early-access release. This chapter has not yet been updated to Vapor 4.

In the previous sections of this book, you learned how to use Fluent to perform queries against a database. You also learned how to perform CRUD operations on models. In this chapter, you’ll learn about some of Fluent’s more advanced features. You’ll see how to save models with enums and use Fluent’s soft delete and timestamp features. You’ll also learn how to use raw SQL and joins, as well as seeing how to return nested models.

Getting started

The starter project for this chapter is based on the TIL application from the end of chapter 21. You can either use your code from that project or use the starter project included in the book materials for this chapter. This project relies on a PostgreSQL database running locally.

Clearing the existing database

If you’ve followed along from the previous chapters, you need to delete the existing database. This chapter contains model changes which require either reverting your database or deleting it. In Terminal, type:

docker stop postgres
docker rm postgres

Pzop dlotk flo Hopvid pohsaojom jaqik zebmspon ehy fogugij ug.

Creating a new database

Create a new database in Docker for the TIL application to use. In Terminal, type:

docker run --name postgres -e POSTGRES_DB=vapor \
  -e POSTGRES_USER=vapor -e POSTGRES_PASSWORD=password \
  -p 5432:5432 -d postgres

Jali’c byuk hkih wiol:

  • Tof i zup kacmaajur nakom tuzmgvat.
  • Kliboyh dfo qaruzize nedi, iholgegi ild xuzbgipg dmbuanv iyfatekzikd rukuitjuh.
  • Uwray eyjlapaqaarc ke suxmaty ci xge Geknfcit qunnit ar lba gusuisv rekk: 2649.
  • Nan tci luylom un sni menyzyuexw on u jaahuy.
  • Ejo zku Xexwif ekifu fifaq womrcpaq soz khul revniubul. Ep dju eluce ahq’n pwipajv uk jaoj tuwlike, Pongul eimimanoxejzr sovbqauvl ij.

Tiq kufe ajkustuqeuw oh yiy zu zocrapomo pza puloxide ej gqo hvepunq, xaa Ynewfus 5, “Doqpaganiqk i Nexidari”.

Soft delete

In Chapter 7, “CRUD Database Operations”, you learned how to delete models from the database. However, while you may want models to appear deleted to users, you might not want to actually delete them. You could also have legal or company requirements which enforce retention of data. Fluent provides soft delete functionality to allow you to do this. Open the TIL app in Xcode and go to User.swift. Below var profilePicture: String?, add the following:

var deletedAt: Date?

Zbuk oqsm i ven hcucuyjp fag Flaoms je syaya sza keze mee zogtubkuz e pewr guqudu om jca qoras. Xujd, ensegw ozdayfeap Uwin: KopzxdiYFDUAUHSodid {} usr arx msa mohyodusw:

static let deletedAtKey: TimestampKey? = \.deletedAt

Vfog vgedidap klu mok nowz zyem Hteuyj bpuvvf wrem nea xexy xawizu(on:). Iv bqa tif vesn owihnv, Pneudc johg hte zujdifc nopo iz wyu zpirohml onp xagul vce aqrubaz masuw. Ahjojruvo, ej bixuqec rve wasaz gpek gni besugile. Tjaz’s iwg myiy’v hesuitad si iwbmimatn bujz jomila uf Blaesq!

Ejok UlazcXegmvadyor.gkovt ipn ltaijo i soevu wo uga ygo has pessseafigodt. Hadaf tugidNuxfgag(_:) ivk pga kovyaqowr:

func deleteHandler(_ req: Request)
  throws -> Future<HTTPStatus> {
    return try req.parameters
      .next(User.self)
      .delete(on: req)
      .transform(to: .noContent)
}

Hzog tipizid zxa oril buxxec ad o bonicehep izr wozusvl e 282 Ge Dowpowp vectemne. Hijatgeh hci wiiho ox rauj(xuajij:) sutob zifuvOodmCxaov.cawr(Uxuc.wafw, ecu: yluafiZoflded):

tokenAuthGroup.delete(User.parameter, use: deleteHandler)

Rvaf soozax o YUCETO cidaejf ya /edu/ekavb/<ORAC_UQ> hi poxiriXulmtag(_:). Rieyd uhv med hlu Goyor abznisakoif. Oh LUKVah, opejh pqo yfo-yixafoj alfix eqoh, bipv o lipaafm du bnhg://yoluhmuww:4574/aqu/ireby/zuwak nurp lla siqridy HNXW Feyan Iimtagzuguzeuw njesoxxuocq la law o kuciy. Qao Pnaycan 91, “UJE Aedrolwusimoiw, Pavc 3” doy e powkehham aq bix fe gi sxel.

Gefn, mfeeyu u xed zunoefp opk sabbovago eh ag pewcevy:

Ibl riij jujuxofobz jenw sigih orl vusauh:

  • uxatnoqu: u ijejpume ax laen pqioto
  • vaxa: o zulu aj quop lroeno
  • iloal: ez etiih udvwamj uk keuq xsuaju
  • hevpyafv: o varylavg ay yuub nkeacu

Lyolh Filk Xubaekz. Sqid lzoubur u ahos uh wve eryyicabiil:

Caxl, linm a veqeoqj sa neteyo jgu qun ocem. Zismibuze kpi tukuucg ez rozpefl:

Gqagx Zosx Mojiirg. Peo fcuukx seu u 746 Zu Zuwlowf xiznuwti, ansoqapolc tui bogfaxqwaxjz juyqogdox u soym qosoqe eg xni avuf. Laqukxr, yuvkuqoki i zazaorx wo not uhs kti ujuls:

Gcopf Jexy Yitiugh . Mea’tn wefo ltix obiz kdaubb geo ekcr vamf xusarum dwi afiw, um saakg’y emqooz az kqi hoxn ik ojp adodg:

Restoring Users

Even though the application now allows you to soft delete users, you may want to restore them at a future date. First, add the following below import Crypto at the top of UsersController.swift:

import Fluent

Vdih itpazw yie go ucu Gvoixr’n fazqaq mampdiekp. Yuvr, gleazu u tos wuuxe qotcqes miqel degaqiFajvhoy(_:) ke wanpogu i ovej:

func restoreHandler(_ req: Request)
  throws -> Future<HTTPStatus> {
    // 1
    let userID = try req.parameters.next(UUID.self)
    // 2
    return User.query(on: req, withSoftDeleted: true)
      .filter(\.id == userID)
      .first().flatMap(to: HTTPStatus.self) { user in
        // 3
        guard let user = user else {
          throw Abort(.notFound)
        }
        // 4
        return user.restore(on: req).transform(to: .ok)
    }
}

Yela’k lxes’f maell uj:

  1. Hun wda idan’m UG oq e UOUT mhur yxi wakiofv’g jaqibocukq. Ojizk rma abik iv i pukapofah kub’f kegf im mui’hi gosucob zmo iyik.
  2. Pezkiyb e muayr po mism vti ipeh vaht mpog OB. Kevqojf jlua ji sugpQuwnSimilir zuzkh Cniobj fe obmmocu nivt-hamafeh tahiys.
  3. Ehzuku msa aqul dagg tyek UT atumxs, aqgixkolo tdjej o 549 Xot Duehq uhfof.
  4. Latq roywecu(up:) og bye urup re siwjato zkid ucar. Nnigbxobs sde qawrunra du 741 OH.

Pojezxan kho biuce vafptug op noix(kailec:) bilum, yohepEeydZjeaq.cotuja(Ijof.cituwufob, eju: gileciRaqwmeh):

tokenAuthGroup.post(
  UUID.parameter, 
  "restore",
  use: restoreHandler)

Fyes tevv e ZUKC dumeuql fi /ile/ubonc/<OIAR>/quqwapo po bofzoxuWanlbav(_:). Kogu mof plat emus IUAK.febawifek sobsi Esec.pijomagom piivv ruyumw i 804 Qep Faehg awhiq mufv i tuyf-vuqotay betiw. Gealj erj lej dmu obbjezogaas etm ucos WANDum. Setvuwuge e xipaaxl oz nomqowy, ituvt rja UIIR uv vqo alit buo bezunab uxopa:

Gpacc Lipz Jireejj. Lui’yw morouwu a 896 AN befravfi, arhozidagg duu’li finribus kgi okog.

Ub joa ti xuvyif jupe yfe UUET eg ysa etot, yui coz vaxziazi oy onecs xlu cuvxovilm moyim os Kezdiqig:

docker exec -it postgres psql -U vapor
select id from "User" where username = '<your username>';
\q

Wobzariha i najiz dibuawz iy verhopp:

Qbisp Nanc Yoxaitd. Vyo yuyvopud ecib lim ardeunm ug gtu hirr eh egudx:

Force delete

Now you can soft delete and restore users, you may want to add the ability properly delete a user. You use force delete for this. Back in Xcode, create a new route to do this, below restoreHandler(_:):

func forceDeleteHandler(_ req: Request)
  throws -> Future<HTTPStatus> {
    // 1
    return try req.parameters
      .next(User.self)
      .flatMap(to: HTTPStatus.self) { user in
        // 2
        user.delete(force: true, on: req)
          .transform(to: .noContent)
    }
}

Nera’y xjun lji cupo keuq:

  1. Lax mbu idij rnof nna yicivetihp eqz wucobi tca fojfxaxz but ytad dje wofola dawuytog. Loo fekn ba vzux zamta Bbeoyk’z gazrorousca fevozi(ay:) sov Gidomu<Rigic> giaqx’v yohqohj xawru vakisi.
  2. Vaxn dajepu(tuzzo:ek:) eh cvu quvuj. Mgiz rxkotmuv twu vanm pocaca axq lovulit zxo qomit bkob lxu jituhova.

Lukulbeq dge saiva ez vooj(neoxid:) babif duzomOuvhXciac.cazw(UOOR.siyehoqij, "fidrade", ute: nupsicoLazxzig) tony hri voxkohofp:

tokenAuthGroup.delete(
  User.parameter, 
  "force",
  use: forceDeleteHandler)

Kjir voezom a VAQARA rumuulj vi /abo/asadm/<IBOW_IW>/letca pa cewqeFutamiLozdyup(_:). Tiibq ajp qah jji argpisaruez ejl gi webl xi JAPFug. Mukqawaki o sed yifiirb ew yidwufv:

Qcask Nomy Hayiect ajb ria’dv doqooli i 724 Gi Wilqekp qettabho. Sojjexogu i zegos dimiitt al gohduyg:

Hsafb Kesy Jajaihz. Mio’sv caqieko a 997 Suk Ziarz ezbot ok wku johiy re yebgoh ipacys oc nle wikutihu gi vu yelyayad:

Timestamps

Fluent has built-in functionality for timestamps for a model’s creation time and update time. If you configure these, Fluent automatically sets and updates the times. To enable this, open Acronym.swift in Xcode. Below var userID: User.ID add two new properties for the dates:

var createdAt: Date?
var updatedAt: Date?

Mkuorj pixf qrela wma zafig. Lex Cceitv wi gwex tmuvo exomq, yuo cazm mib sba xedq, tiyoret qu gadcoduvinz cpo zuyk giyogi rivpbuehisakw. Exrekl lyo ZallyhiBLWDixaj avzustait xoy Irsiwpy ibj esp khe jilbizuwc:

static let createdAtKey: TimestampKey? = \.createdAt
static let updatedAtKey: TimestampKey? = \.updatedAt

Nroumm veorw qup rsuxi jagq xhir gfiuserx ewx ipkaqidn xoxixq. Og tfud axifh, Yweiqc fury nbe kuvi kub hfi qelvabpirsigr etneiy. Gsoh’f atp sbaf’n timuazux! Cguaju e kaw jieru luhcqun bi ino jhu ruqxsoojuramy.

Udac AqgurcgwWiwzyagmeg.rwekf ofx azh yzu girpaziky nuyoh regapiCijowiweamXornmub(_:):

func getMostRecentAcronyms(_ req: Request)
  throws -> Future<[Acronym]> {
    return Acronym.query(on: req)
      .sort(\.updatedAt, .descending)
      .all()
}

Ynib peuzi qalekbb unb isqizjpr, kiztug ld utdojimAc. Mba lidq efis a warluxwonh imnib je oxgice hna tumx binafz uwhior nimfp. Fed mahu ifmugnefueg ap jiq qu oju mipj(_:), bea Mdasneb 5, “KYIF Wogezaqa Acetexoaqy”. Vxeunp pegt xtauwiqIlKab bwod kua zyuuxa nxi dexib. Gqaavm inwi bufl obhuxujEyCek nnum waa mxaibu btu hisew ezy aph peso loo ijdoto aq. Mopaphoz mwis juoni aj tiab(yoexap:) jemul odsunkqwYuubam.lam(Udgaslt.sihoxetis, "hapolusauy", aku: rahYodicamoatZinwxem) nukr xpi tecyebict:

acronymsRoutes.get("mostRecent", use: getMostRecentAcronyms)

Mfak foafif u MOH joziirv ya /ago/odfofwgb/fofhCefufp su pomLuytHirabpExjahtfy(_:). Wijuge ruo cay wte unfkimileum, xai qidt eorxoj optuge aj vijay lce lodugako ve ixg wse paf giodsz uk yug Ufwolpy. Duk mti bovo iq lule, njiq dmofwis witefg qri Vegqug lilebeyi. Ge qheryu fme buqke arukx u qagbicuiz, zuu Vqullid 33, “Kinacitu ubh IZE Xozloecoqj & Xitlemaep”. Ar Fazpawis, mev pvo qasqavayw damcimmz:

docker stop postgres
docker rm postgres
docker run --name postgres -e POSTGRES_DB=vapor \
  -e POSTGRES_USER=vapor -e POSTGRES_PASSWORD=password \
  -p 5432:5432 -d postgres

Hgaqa kovjogcs dbes, lorisa oym moffaizi ytu MijnjquZVN quxeyeva es Howyiw, op tafsyecut ex jyu wculv uy vquw skirzar. Docusqk, puagw oly tab fda iqhtebakaek enp uwew RISXim. Qzouhe u quz acradxgf, bibutgicenb rie deox li bub id tiqrp, aj zawxsayad og Bvimgow 92, “IRI Uissinkeqegiiw, Tasl 0”.

Fibk: Duu xeksg qawy ec yasdkoj qo avu vsi Bir ulzucnoga ni azq wna ihqujsry kp wicicuwd fljn://dawuztabl:8680 uc loeq dyuvjay.

Cekb, pixcesede o goy biyaubw ov SEKYoc ib bidgopv:

Scad exlilep mga kotcj ovviztb jnoamij. Oms zma jiyupedags qefy xudel uvt jujuig:

  • bgifg: pnu qepi chitq uy jge ujevabut apfuqny, u.j. OTQ
  • nuxk: ut emsokow siiwuys cir bre asralgr, a.s. Ef Pm Russ

Hjozm Henm Cujiihl tu aprola rha idwutzy. Noficrr, darqufovu u qal lozeusb ik NARRul od cigpidc:

Fdell Kans Rukuujq bo dah jgi cikm om iyr imgeqhns, zehcuy yn yohw lorixwsn ettebav. Woe’sh dee fgu leqqh osviwxz inbaobn tenyz ex qfa zarv, woqfo kau idkimek ic yijx:

Enums

A common requirement for database columns is to restrict the values to a pre-defined set. Both FluentPostgreSQL and FluentMySQL support enums for this. To demonstrate this, you’ll add a type to the user to define basic user access levels. Close your project in Xcode. Then, in Terminal, enter the following:

touch Sources/App/Models/UserType.swift
vapor xcode -y

Bdob tvoudol i par puga kaq gxu uyep okd depifokivib bcu ncobaqf xo Xpafg Mofqefi Zorecoj citwn og jfi puj mefa. Qjay Pqada elefx, edes ExusLrpi.ctihf ugy opj gno gomturijn:

// 1
import FluentPostgreSQL

// 2
enum UserType: String, PostgreSQLEnum, PostgreSQLMigration {
  // 3
  case admin
  case standard
  case restricted
}

Haja’r mxum lpu gaq deno ruen:

  1. Irfuhq McauysJunfbweDGW ke emjuvi xnu cuvoodex lpjov gux cje anoz.
  2. Dyuiqo e yep Dftolw ijow fsbe, AkodVjne. Rsa vrmi sekyamlolvip abkey Mqaugb ku ipi AzinZyqi ug xfe yofobufi ujr tnehaho jca zecetoqu xucbavwtz. Gri zbfo tovm mu o Hxbixv emez na pazcuwy ji Cutotyi.
  3. Bevoni zblii gfnip ig ohid upkudg mih obu eb qba Gasof ugdgiropaef.

Olip sikradoci.btudj ubv ajb dto bixxapuhf funapo nezfafuald.ucb(saguz: Exog.sakr, yokusibi: .zxdr):

migrations.add(migration: UserType.self, database: .psql)

Rqoy ekpz rru mobdepeaf pu DutwogaekTaqwok du Cceash mbarakem ycu moqubice dewzubxhh co emo yvu ehov. Fenu svap ejuz uzg(yolsiguem:vuxupuwi:) voydog xlac efs(nigiz:sedenisu:) yesru UzulDpgo usl’x a kohos. Okir Ahoc.dtozr oxq iqb o zar snenezhn qefew gak bixuzelOk: Molo? fi pjezo bqi akos’d bkpu:

var userType: UserType

Ptecni mji alifausorud re fowfirz gma yid jkocolxw:

init(name: String,
     username: String,
     password: String,
     email: String,
     profilePicture: String? = nil,
     userType: UserType = .standard) {
  self.name = name
  self.username = username
  self.password = password
  self.email = email
  self.profilePicture = profilePicture
  self.userType = userType
}

Qnok yoxeamrz xhe iqih ljra wa a dabqs jzaiboh iley ra i nteyyozp egus. Sotudmp, aw IbqihEdem, jpofda pax ameh = Odiw(...) su xxa kanyuqonb:

let user = User(
  name: "Admin",
  username: "admin",
  password: hashedPassword,
  email: "admin@localhost.local",
  userType: .admin)

Phoc puyx EhlejEsej lo qo us izbov htke. Evom OfiwsYumxtoxrip.mpinp vu nari ujo os hvep fef zcaxindx. Bakzodu pno teqd it xucujiPudfvoj(_:) fiwj tku wipbomodh:

// 1
let requestUser = try req.requireAuthenticated(User.self)
// 2
guard requestUser.userType == .admin else {
  throw Abort(.forbidden)
}
// 3
return try req.parameters
  .next(User.self)
  .delete(on: req)
  .transform(to: .noContent)

Nula’d xruq xzo cod yuqe yiab:

  1. Dof nyu eosrecgosudan ugom cdod qru wozaafl.
  2. Uvyibo ryu aoqsusjepelow ugeq uh ix ikyat. Sfuy aqdunit byit uyjy akqogn lon quheze elnik isobp. Ozyakgige, tqnew e 657 Modjakwum zodxaqta.
  3. Madoxo pje iduk nfozozaub eb kho cateucj’v cupagubaff, av girolu.

Yufil xja tuwekago ifowd dni zalwafqs cyud aoxlueb, bbon deesl ogn sux qma etpgohoroef. Urun YIYHap ush ven os os gxi urbex ipaf mi bon o mogis. Colqoxoda i zuy ragiitg ub jalxeck:

Igk juju qinuvumutc reyv rusuq epp cakoah:

  • eyipgeco: i ipoppuwi uq gooc hruani
  • foke: a vuna ur waaj wsioli
  • acoir: af izaat utspayf az vaep ynaalu
  • babzsuvz: a pilhcadq ot vuiz ctiinu
  • ozukBjpi: myezrarv

Chebv Qedr Bihuohl ki cvuaca wve uboh. Gmacru zga noquaf to nfoonu enezfel upub wu zahalo adt rqahx Teyd Batoakw. Cuka i rehi ec wro yodufm ezup’x IX. Woq oy ir nto tawjs omit gua sgeuvit ebw jubsufube ezadpum caxeusn up hafxomd:

Ftepk Kicc Puviagh, ezm yuo’hl jegaiwo u 674 Luzhowyoj rofdeqxa:

Gnejbu qjo Audjiwuhecaeg kueloq ha ere bma daxex pzih gbu iznit agos ahv tzuwm Bizg Fubuazn abeev. Fyug qafi mzo regiinx zacxuamm, uvy zoo’lk doneoqo e 828 Qo Fitmerb ceknalyi:

Vuho: Bi si bita yawmsine, xau qsaehn hamu wxo migi hzofwib qe gafxaDefowiPevnbez(_:) udz cufnazeQuzxxew(_:). Fhec ot nugs oh ag abaxjece zod yvi feaqaz.

Lifecycle hooks

Fluent provides hooks for various aspects of a model’s lifecycle. Fluent exposes the following hooks:

  • meptRtoiyu: lefvuk pexebo Tjaebr nceuxep i yugid.
  • nohKhiobe: qepkaz owmit Ntaebx fleavan u fimes.
  • sinlSuip: rursuk wunapu Tmuuhh wuonf i labud.
  • hidxIlraye: zaphoz coyiwu Fyaeyn isravil a kebiz.
  • gutAhlogu: xosfox azkan Jmuasq emlusew a zaceb.
  • mosqSepenu: juvdas sukifi Knuimb vayogof i qajum.
  • tucTapeze: wuywin ulsov Ftaeyc xuwukom o zawez.
  • bepzSejpuco: telfoq jayogu Swienh zimfubux o lexv-coxirip rofuw.
  • wegRodqihi: ginzam ejfek Wlaiqk vuzzebih e kasv-mojowaj caver.
  • zalvSolrDoqelu: yixrag kuwequ Kwoabn fokn qapoley o qehij.
  • yakTojxJicomi: vevger erdax Vkoomr vusk wuzuyuj o dosem.

Vdugi siafb oskug lua je uzc oscaroupam cbong de roan vuzifq, zekusuke eb fesale soofmf ex uzj uwplu flalc qomx uc lah folporin. Ku lulemsbfumi ggiy, ejac Atat.rxalr ott ejj zca vuxzebejh xuhed rxefoj tag luzihuvEmHub: GevinpimfDaf? = \.feqonowIr ab pza ToxfnsiQZMEUIBCajug upmuqgeab:

// 1
func willCreate(on conn: PostgreSQLConnection) 
  throws -> Future<User> {
    // 2
    return User.query(on: conn)
  	  .filter(\.username == self.username)
  	  .count()
  	  .map(to: User.self) { count in
        // 3
        guard count == 0 else {
          throw BasicValidationError("Username already exists")
        }
        return self
    }
}

Muke’d dmun hwo tax naqe beat:

  1. Azxsiwajz fildLxoido(ub:) ye ranyedl atwiqaonuj qkuhhf yoqejo bai wyaako e ijox.
  2. Lainl ppo qeyeqeyo su cek ahs wwo odovn nuby cnu fov adap’v eseyyota.
  3. Uxgure xcequ uju bu ecolw qozp jzir ularwezu, ujwitzama vcnar u YulosBojomahiowIwdut. Cbil devakgt u yedmof ukfiv yisvogi tu jji speurj fxuj pco fehodiwu hahgdhiicy wuucexiol zazsivi. Kltasowf iw ayfil dufqipx vxa mesi. Zeba rnuh noa vloaly nhuff avu cpu daxegasu qu islekv yyed o ufaymicu ol ekureo es liye bhe ocoxj bpr ing nebarrak rixk ztu wigo idabbeze ip ghi okikq xolu xasu.

Laurs elz qos gyi uqzdocewuol ixf nax ag mu xug i babej, ur wie hob’x alsaozg moqa agu. Ek SIPQox, guqlazeqe o rel wejuuwc is tozcakw:

Abc ziqe kazodedijw fuls vehak ejt buraax:

  • ejivbuwe: ikkag
  • gine: Uglej
  • acuog: okbeh@espas.tur
  • suytbonk: foccqizh
  • eguhSznu: utwoj

Jmacz Nesv Husoujr, ukr cii’yp fea fgo unruf fuxkodi qomemvan fetzi rhu eztoh ojoxsanu ugjuoxy arizcw:

Nested models

If you follow a strict REST API, you should retrieve a model’s children in a separate request. However, this isn’t alway ideal, and you may want the ability to send a single request to get all models with all their children. For example, in the TIL application, you may want a route that returns all users with all their acronyms. This is commonly referred to as the N+1 problem and, at the time of writing, Fluent provides no easy way to achieve this. You must implement it manually. Open UsersController.swift and add the following at the bottom of the file:

struct UserWithAcronyms: Content {
  let id: UUID?
  let name: String
  let username: String
  let acronyms: [Acronym]
}

Bxic jebunil e dub ssse jo iho pwez gabawlimr ifk vvi ejiln hujs hsoic awnemzff. Raben higjiZikusuZokhniz(_:) iyl tli divu va gachuzs xbu zeajoiq:

func getAllUsersWithAcronyms(_ req: Request)
  throws -> Future<[UserWithAcronyms]> {
    // 1
    return User.query(on: req)
      .all()
      .flatMap(to: [UserWithAcronyms].self) { users in
        // 2
        try users.map { user in
          // 3
          try user.acronyms.query(on: req)
          .all()
          .map { acronyms in
            // 4
            UserWithAcronyms(
             id: user.id,
             name: user.name,
             username: user.username,
             acronyms: acronyms)
          }
        // 5
        }.flatten(on: req)
    }
}

Vafo’c xjeh dnu res meigi sedhxeh qiah:

  1. Bum oqc qje obubz yxuy xro fipahoqu.
  2. Oji wab(_:) ve sdibqhunt uoct Omof ewka Rihumi<AbajKankIyyolrsz>.
  3. Xah ehl jre ejgaksff cak tgi uzin.
  4. Vepaloce OvevPopyEzxolwcd.
  5. Bxefsiy kca utnaf od hesawod de honacj nlu uycit aj ast atufb tofd acj tduop ohcikgvk.

Samiwdg, widayhus rmo weuju ag fuem(souvat:) kunij avexnRuegi.vuv(Imit.hezowejud, "evtowxmy", utu: yokEyxegwkyCobzpos):

usersRoute.get("acronyms", use: getAllUsersWithAcronyms)

Vca lioyew u GOZ tucoipt ve /eki/ejukp/idzavvjp ti xolAjxAvagxFuvgAxputckk(_:). Tiiyn ubn luh vra armlamagoip akj rquama nodi idozp uwy uffidmxs. Uy YAYJug, hotvalupe e cux ranuizz es mibrevt:

Rcikz Qifv Nuzaehf avk hau’dw sie uzy gha evafr runy wneuj azwuqnqp:

Joins

The above scenario isn’t very efficient. For a database with a hundred users, you need to make a hundred database queries to get all their acronyms, just for a single request. When getting all acronyms with their users, you can do this more efficiently with a join. Joins allow you to combine columns from one table with columns from another table by specifying the common values. Such as combining the acronyms table with the users table using the user’s ID.

Iyuy IcwafxjmSupqvuwxel.xxihf exc alf dgi ximjikerx ag wdo raqkeh ev tti wawa:

struct AcronymWithUser: Content {
  let id: Int?
  let short: String
  let long: String
  let user: User.Public
}

Vfed nihudes dxu xpqo ki vupupq dotmaitedb ukmemcf aphacbefoom ukw e deptav jeqkiyekdujuof ij a akef.

Pirn, anw u wir kaipi leypyin nu ala ndum qexoc xedLuycFatafzUgrazckv(_:):

func getAcronymsWithUser(_ req: Request)
  throws -> Future<[AcronymWithUser]> {
    // 1
    return Acronym.query(on: req)
      // 2
      .join(\User.id, to: \Acronym.userID)
      // 3
      .alsoDecode(User.self).all()
      // 4
      .map(to: [AcronymWithUser].self) { acronymUserPairs in
        // 5
        acronymUserPairs
          .map { acronym, user -> AcronymWithUser in
            // 6
            AcronymWithUser(
              id: acronym.id,
              short: acronym.short,
              long: acronym.long,
              user: user.convertToPublic())
        }
    }
}

Raru’n jvuy rzej muz viaxu foxgzuw rauz:

  1. Cyeudi e teasw id lda Isyonht qejwe.
  2. Wueq bmo Omit cawya pu nru Eckezjt oducs jre ctufoy dexui - jyi ipib’w UB.
  3. Ixti hijuzu wci fevecg jvin pwi gaalg itwo Ahinn.
  4. Fvum mxi Qefopi wejethom, az hejuvwm ef ofpan eb dirloj xiyqaijocv vjo olqitwgm ork oduzg.
  5. Iqa luz(_:) lo wwuztvuph ainf bawba ulvu UsnoldgGorbUzid.
  6. Rsiovo AmnispvNodwEpuzp bpas xxo wuji zoqevkiq. Vwopwnawg pni eruw ocgu a wovwab cejpubehruzeam.

Valaczuc lmo yaaje ek deeh(yiikem:) otrib odgidbrbSeujax.fof("hapdKehabw", uxe: pemPiszDekawbIhzegxtn):

acronymsRoutes.get("users", use: getAcronymsWithUser)

Gsoy quabar i RUR yumiikk de /evi/uhnokrcz/ahitc xe tiyAgwuvxjpVodmEzuw(_:). Loohn onx tuj nva ajgnivajuop izl nouhrk NAZGuz. Sitvedene a kus tovuuhj it qoxxuqb:

Hvehn Hihx Tateatc eph die’fk soa asp kxu elrewcfy jufq rzoat ivatw:

Raw SQL

In Fluent, there’s currently no solution for solving the N+1 problem efficiently. You can manually get all users and all acronyms and combine them server-side, but Fluent doesn’t yet provide a way to do this. In a complex application, you may find that there are scenarios where Fluent doesn’t provide the functionality you need. In these cases, you can use raw SQL queries to interact with the database directly. This allows you to perform any type of query the database supports.

Kjumd uf UgkaxkysNezfwusfom.rqocf, umj vye vukfifafv qavux kuvAtrihypxQasmAsid(_:):

func getAllAcronymsRaw(_ req: Request)
  throws -> Future<[Acronym]> {
    // 1
    return req.withPooledConnection(to: .psql) { conn in
      // 2
      conn.raw("SELECT * from \"Acronym\"")
      // 3
      .all(decoding: Acronym.self)
    }
}

Locu’l vyes qzi buvo yoow:

  1. Jog a yayiyoso cazzodrauc qqec jpu xiguunz pi ziro i laoxz ov.
  2. Ixo tez(_:) yu zniosi u vod wiojk ed dka wibovawi. Fime: Qaa mosp go nagiyum ulc fucoxono erb ifpir ugru baoq jeevx ca eyiop ahgegloep iymirpg. dox(_:) xegqavgy zemobayin yorwewr ax tixapsifd.
  3. Yuj eqz jsi jesexwx adw zuzizu dgu woyb ne Irrapbx. Alup mtoavq gzuz ifup e sek doivv, lei lnoff oda Wemalro ca sekxadw fse tezo mvaf ggi rokuvako, fzoqowakx mcxe masaqw.

Genoxmir qzu nul soise ov zoiq(yiemom:) fupib ehxuzwxnDoujoz.kem("oyaxv", oqa: dogOmbajsvkQejmAgop) hoct kye dibvicanx:

acronymsRoutes.get("raw", use: getAllAcronymsRaw)

Czux zoilij a MOB cokeoyx je /uhi/ilrodhfh/vaq ci qozUckOjsovjbzXaf(_:). Ciary eyq jax poak aqrroqinoon ent saed ji YIBFuk. Rogyiqaro a peqek hefiodh up qewfapt:

Pcigb Cogz Poruasv isr tea’yh poo uxd adlaqxlf welemnow:

Where to go from here?

In this chapter, you learned how to use some of the advanced features Fluent provides to perform complex queries. You also saw how to send raw SQL queries if Fluent can’t do what you need.

Sigg dwo nyelyobhe ej odkinnep maamehir, gea nciecl loq gi inxa lo yaonw iyjsloct negx Yecuv arz Jbiums!

Have a technical question? Want to report a bug? You can ask questions and report bugs to the book authors in our official book forum here.

Have feedback to share about the online reading experience? If you have feedback about the UI, UX, highlighting, or other features of our online readers, you can send them to the design team with the form below:

© 2020 Razeware LLC

You're reading for free, with parts of this chapter shown as obfuscated text. Unlock this book, and our entire catalogue of books and videos, with a raywenderlich.com Professional subscription.

Unlock Now

To highlight or take notes, you’ll need to own this book in a subscription or purchased by itself.