how to query from multiple tables in sqlite.swift

Question

Is it possible to write a statement in sqlite.swift that will generate the equivalent sql:

SELECT foods.name, food_types.name FROM foods, food_types WHERE foods.type_id=food_types.id LIMIT 10;

I can't figure out how to query from multiple Table objects at once. Thanks!

Answer 1

Your original query passes two tables to the FROM clause, creating an implicit join. SQLite.swift's query builder language currently only supports explicit joins.

Check out the documentation under Joining Other Tables for more information on joining tables.

In your case:

let foods = Table("foods")
let food_types = Table("food_types")
let name = Expression<String>("name")
let id = Expression<Int64>("id")
let type_id = Expression<Int64>("type_id")

let query = foods
    .select(foods[name], food_types[name])
    .join(food_types, on: foods[type_id] == food_types[id])
    .limit(10)