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!
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)
.joins can be chained, so you should be able to continue to build up more complex joins.
I figured it out. The foreign key is a column for all the tables I'm trying to join, but there are foreign key members that are not common among all the tables so I believe sql then generates a a cross join vs an inner join... That leads to all the extra rows in the query. I confirmed this by using the sql that sqlite.swift generates on the db directly.