Missing nested where clause
If you see here i'm missing the ability to do a where filter on the nested
rawDetails
table
The docs say you can do so
My relations below
![No description](https://cdn.answeroverflow.com/1226651441964257280/image.png)
16 Replies
@DiamondDragon you will find there is no where property because this is a one-to-one relationship. The example you cite from the documentation is a one-to-many relationship.
If you want add a where clause for a one-to-one, or many-to-one relationship, you need to use
exists
with a subquery in the top-level where clause.im trying to understand this better. How should this look? Getting an error
SQL input error: no such column: raw_cards.id (at offset 636)
with this but sounds like the query would looking something close to this?
Fortunately i was able to get the right query i think using a select with left join@DiamondDragon with regards to the error: is your db in sync with your schema?
yeah should be. This is
raw_details
![No description](https://cdn.answeroverflow.com/1226697126369824828/image.png)
@DiamondDragon and what about
raw_cards
?@Aaroned
![No description](https://cdn.answeroverflow.com/1226697680588247041/image.png)
@DiamondDragon can you use
to see the SQL that drizzle generates
Query:
select "id", "z_id", "county_id", "z_update_date", "z_is_new", "z_is_updated", "last_fetched_date", "created_at", "updated_at", "deleted_at", (select json_array("z_id", "raw_data", "last_fetched_date") as "data"
from (select * from "raw_details" "rawCards_rawDetails" where "rawCards_rawDetails"."raw_cards_id" = "rawCards"."id" limit ?) "rawCards_rawDetails") as "rawDetails"
from "raw_cards" "rawCards" where exists (select "id", "raw_cards_id", "z_id", "z_update_date", "raw_data", "last_fetched_date", "created_at", "updated_at", "deleted_at" from "raw_details"
where "raw_details"."raw_cards_id" = "raw_cards"."id") limit ? -- params: [1, 1]
this is using exists so should be notExist but still have that error anyways
what i notice with this is it returns all the rows from rawCards when it should be just one row
@DiamondDragon yes I wonder if this is a bug.
Your table name is different to the name of the object in the schema ("rawCards" vs "raw_cards") which is fine. The relations query then aliases raw_cards as rawCards, but the subquery doesn't have the alias, hence the error. I might try and create a simple repo and log it as a bug.
yes this is correct because you've hard coded an id, and this subquery will return true for every row, hence return every row
In the mean time, if you want to return the first rawCard that doesn't have a rawDetail, then yes you can use left join and test for null
ah yea, i understand that now . the exists in that returns true every each row then returns all rawCards.
cool thx for your help and filing the issue. always interesting to find edge cases with drizzle 😄
im basically querying for data that doesnt exists on table B but table A has the id (and also if data not updated within last X days. Not sure if this is the most efficient way but seems to work well )
yeah left join will work well 👍
sorry @DiamondDragon I just realised it's likely not a bug, you probably just need to use the callback syntax:
ah yes this seems to work.
why do the docs pass through in teh 2nd parameter the operators? This to work fine without passing them in. Also wish the docs explained a little better what the 1st parameter means (seems like it's the table being queried?
yes the 1st parameter
fields
is the table you are querying, and the 2nd parameter operators
is just a shorthand to using operators without needing importsquick question, im running a scraper so i am running this file every now and then which uses this query to get the records to scrape. noticing im doing a ton of reads. Im not familiar with indexes but should i create an index on a few of the columns here?
![No description](https://cdn.answeroverflow.com/1226991019472715878/image.png)
@DiamondDragon https://www.sqlite.org/optoverview.html this is a good page for understanding SQLite (ie. Turso libsql) query optimization and indexes.
SQLite does NOT automatically create indexes on foreign keys, so if you are not sure, that is generally a good place to start.
Another helpful tool for optimisation is
EXPLAIN QUERY PLAN
https://www.sqlite.org/eqp.html this can be run through the Turso CLI db shell command. (You will need to first obtain the SQL of the Drizzle query by using .toSQL()
at the end of the query)
You really want to avoid TABLE SCANS
as each row is read in a table scan and counts to the Turso Rows Read quota. By adding appropriate indexes that SQLite can use for your query, the query engine will use INDEX SCANS
and avoid TABLE SCANS
.
See also https://turso.tech/blog/tips-for-maximizing-your-turso-billing-allowances-48a0fca163e9Tips for maximizing your Turso billing allowances
Make the most out of your Turso usage allowance, and boost the performance of your SQLite queries at the same time, using indexes and triggers.