Joins / DB views

Hello,

I see I can create tables with foreign keys, but I don’t see a way to write queries with joins in the frontend. Did I miss something or is that not implemented yet?

Or if I could define a view on the backend and use that in the same way as a table with useQuery(), that’d be great :slight_smile:

1 Like

Joins are not supported yet, but will be in the future :slight_smile:

We’ve also looked into adding support for views. To make views work well with the row level security policies, we need the security_invoker feature of Postgres 15 (which is not out yet). Or we need to implement some kind of workaround.

Thanks, that’s good to know.

For me, by the way, the views would be of greater benefit as I’d generally prefer writing queries directly in SQL rather than via the client library functions- I can execute them in a database tool of my choice, I can use various built-in functions, I’m less dependent on 3rd-party code, and users can’t see the queries I’m using if they dig into the code.

1 Like

tried to get a foreign table value but failed, guess it is due to this join feature not implemented yet?

am getting this error:

@exiang yes we don’t have joins yet. You need to manually fetch the task’s user like this:

function Task({ task }: TaskProps) {
    const user = useQuerySingleResult(query('users').filterWhere('id', task.userId));
    return <div>{task.title} created by {user.email}</div>
}

I would also find joins useful. I’m trying to query a many-to-many.

There is a whereIn function in the source code, but not exported. Is it functional?

Sorry, it’s indeed broken right now. I just pushed a fix via Finished implementation of whereIn(field, values) by mpscholten · Pull Request #67 · digitallyinduced/thin-backend · GitHub

I tried this but whereIn haven’t exposed yet

image

Did you reinstall the types package? (See TypeScript Type Definitions - Thin Backend → Updating the Types).

If you have already done that: Go to the Types page and check the “Last updated …” text on that page

If the types are older than a week, try to change something in the Schema Designer to trigger a rebuild. After that install the latest generated types.

1 Like