3

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!

docho
  • 71
  • 1
  • 5

2 Answers2

7

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)
stephencelis
  • 4,954
  • 2
  • 29
  • 22
  • Thanks Stephen, I've tried that and it works, I'm running into trouble when I try and join more than two tables, I have multiple tables all with the same foreign key and I'm trying to join on it but I don't get the expected result, I get way more rows than expected. I can do two at a time, and then build my data structure from there, but I was looking for something more elegant. I really appreciate your work and your well designed Sqlite.swift. Cheers. – docho Apr 04 '16 at 23:16
  • Without an example of what isn't working, I unfortunately don't have the ability to help further :( `.join`s can be chained, so you should be able to continue to build up more complex joins. – stephencelis Apr 05 '16 at 16:25
  • Thanks for for the follow up, I think 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. Thanks again and I'll close out the question – docho Apr 05 '16 at 18:50
  • @stephencelis. I'm not sure anyone in the universe can answer this one other than you: http://stackoverflow.com/questions/41874682/subtle-cast-warning-when-using-sqlite-swift-binding-to-any !! I'm stumped. – Fattie Jan 28 '17 at 18:42
  • SO well written... thank you! I'd been looking for a good example on how to do this for months and had always just hacked my way around it and now its very clear. – Jc Nolan Aug 11 '19 at 19:39
0

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.

docho
  • 71
  • 1
  • 5