AnswerDotAI / fastlite

A bit of extra usability for sqlite
https://answerdotai.github.io/fastlite/
Apache License 2.0
163 stars 14 forks source link

can you join dataclassed tables? #13

Closed swyxio closed 1 month ago

swyxio commented 3 months ago

i have 2 tables, post and source, and i know what to do with them in normal SQL:

    postsview = f"""select {post}.*, url as source_url, {source}.title as source_title, {source}.description as source_description
    from {post} join {source} on {post}.primary_source_id = {source}.source_id
    """
    db.create_view("PostsView", postsview, replace=True)
    postsViewLimit = db.q(f"select * from {db.v.PostsView} limit 10")

but obviously the result doesnt have the nice dataclass __ft__ sugar. is there a way to do this with Dataclass stuff? can we document please?

pydanny commented 1 month ago

Hey @swyxio, the MiniDataAPI Spec that this library follows doesn't include joins. We document that here: https://docs.fastht.ml/explains/minidataapi.html#limitations-of-the-minidataapi-specification

If it helps, at some point soon I'll put in some work on fastsql. While that also follows the MiniDataAPI Spec, it is a wrapper around the lovely SQLAlchemy library which does support foreign keys and supports SQLite. As soon as figure out how to do it I'll make a point of documenting using FastSQL with foreign keys by going right to the SQLAlchemy.