Inner Join between tables

[g3rfx]

Hello,

I wonder how the syntax would look to perform an inner join for two tables without loading both tables to perform the join in memory. For example, something like:

table1 = db.table('table1')
table2 = db.table('table2')
db.search(where(table1.ID == table2.ID))

Thank you in advance.

[msiemens]

Hey @g3rfx,

TinyDB doesn't support join operations. Not just that implementing joins efficiently would require implementing indexes which would increase the code size a lot, but TinyDB reads the underlying database file on every read operation. So you're loading the whole database in memory anyway.

Do you think this behavior (loading data on every read operation) should be documented better?

[g3rfx]

Hey,

Thank you for your reply (and your great work!).

Yes, this is the same way I used to join two tables in memory meanwhile (using the indices).

I think the documentation is okay and someone with the same question will read our discussion anyway.

[therden]

Hello @msiemens,

For whatever it might be worth, I think that it would be worthwhile to add a description to the docs of how/when TinyDB reads from and writes to the default underlying data file.

I believe that uncertainty on those points raises other questions (like this one, for example) for persons who come to TinyDB having some familiarity with databases like SQLite.

Do you think this behavior (loading data on every read operation) should be documented better?

[msiemens]

For whatever it might be worth, I think that it would be worthwhile to add a description to the docs of how/when TinyDB reads from and writes to the default underlying data file.

You're right. I'll add this to the list for reworking the TinyDB documentation!

[madeinoz67]

Further to this topic, being a very new TinyDB user, is there an example somewhere of how people are doing joins on tables as alluded to by @g3rfx ?

Thanks