1

I use SQL.swift but I cannot find a way to select values from multiple tables during a join. My db structure is the following:

airport --* tower --* tower_frequency

An airport can have several control towers and each control tower can have several radio frequencies. Given an airport ident, I want to select all radio frequencies.

I build the query using 

let airportTable = Table("airport")
let uid = Expression<Double>("id")
let ident = Expression<String>("ident")

let towerTable = Table("tower")
let frequencyTable = Table("tower_frequency")

var query = airportTable
    .select(airportTable[*], towerTable[*], frequencyTable[*])
    .join(towerTable, on: airportTable[uid] == towerTable[fkAirportId])
    .join(frequencyTable, on: towerTable[uid] == frequencyTable[fkTowerId])
    .filter(airportTable[ident] == icao)

The generated SQL is from query.asSQL() is

SELECT "airport".*, "tower".*, "tower_frequency".* FROM "airport" 
INNER JOIN "tower" ON ("airport"."id" = "tower"."fk_airport_id") 
INNER JOIN "tower_frequency" 
ON ("tower"."id" = "tower_frequency"."fk_tower_id") 
WHERE ("airport"."ident" = 'SBP')

and it works fine when I run it manually in the db, yet SQL.swift throws the exception No such table: "tower"

Jan
  • 7,444
  • 9
  • 50
  • 74
  • 1
    SQL.swift is probably right. But without a description of your schema, it's hard to tell what's wrong. Perhaps your tower table is called "Tower". And if you really want the frequencies, you'll have to select them too. – Gerard H. Pille Jan 23 '18 at 14:56
  • sorry, that was a typo in my sample code. I do select * from all the three tables and as I wrote, running the same SQL in the db directly works fine – Jan Jan 23 '18 at 15:05

1 Answers1

0

I found out that this happens when I use the star query frequencyTable[*] on the second on third table. When I use an explicit query it works : .select(airportTable.table[*], frequenciesTable.table[frequencyCol])

I'll open a bug in the project

Jan
  • 7,444
  • 9
  • 50
  • 74