Help with improving database query
Hello, I am seeking advice on how to improve a database query I am currently working with. My objective is to get the data of a prompt using its ID, and this data includes the names of the tags associated with it.The currently way I am doing this works, but feels hacking so wondering if theres a more efficient way to achieve this, possibly through restructuring the database or altering the query itself.
Below are the definitions for my "prompts" and "tags" tables from supabase DB:
26 Replies
My query
This can be done in one query
Just use to innerJoins in the first query
That should work, but an even better approach would be to use drizzle's relational queries
I tried this but getting this error:
`
Using relational queries looks awesome and does look to be better approach but I am getting en error with it as well ðŸ˜
but this is due me being new to DB and not knowing how to do things properly
this was the error i got
My schema's
and my query
Ok, I see now.
Can you log the resulting query as drizzle builds it and copy it here?
I think the problem is that your relation Prompts -> Tags is not a traditional SQL relation.
Instead, you saved the tagIds as an array in your prompts table
Basically throwing the relations benefit out the window
Seems like Prompts -> Tags relation is one-to-many
The canonical way of defining that relation is by adding a
prompt_id
foreign key to your tags table, and eliminate that tagIds array from the prompts tableyes here it is
have to break it into two parts due to text limit on discord
part 1:
part 2:
ah ok so i am guessing i would have to do a many-to-many relation if I want a prompt to be able to have multiple tags, and tag to be able to be assigned to multiple prompts?
Well, that is the canonical relational way of doing it, I don't know if you have an architectural/business reason for the way you did it
I did it that way partly due to my lack of experiences with DB's and so that user can be able to assign as many tags to a prompt as they want from the list of tags they created
how would i go about doing this without keeping track of the tags currently assigned to a prompt without an array?
You need to create a intermediate table for your many to many relation to work at db level
See here the Drizzle way: https://orm.drizzle.team/docs/rqb#many-to-many
Drizzle Queries - DrizzleORM
Drizzle ORM | %s
ah ok i'll try that out
There's also plenty or resources as it's a fairly common concept in relational databases
@king.edwards Each tag would be added as it's own row in the tags table instead of being in an array in a single row.
prompt -> one-to-many -> tags
If you wanted a standardized list of tags and then linking those to a prompt you would want:
prompt <- one-to-one tagsAssignedToPrompt one-to-one -> tags
tagsAssignedToPrompt would be an intermediate table containing the fields promptId and tagId. You would have one row for each promptId+tagId connection
I have a similar setup linking products available to a program through an intermediate programProductLink table:
ok so say if
<prompt1> has <tag1>
<prompt2> has <tag1, tag2>
<prompt3> has <tag1, tag7>
that would mean 3 rows is created in the intermediate promptTagLink table?
That would be 5 total rows:
ah i see, thank you for taking the time to help and also make that visual !
that would mean i would take more more space in the DB, would that be a problem if each year has about 100 rows in their promptTagLink table?
also if a tag get's deleted, i imagine that i would be able to delete all the rows that had it in the promptTagLink table with a trigger function?
Are you using MySQL?
posgres
I am more familiar with MySQL, an "on delete cascade" in MySQL would accomplish that
I imagine the same is possible with postgres
the
promptTagLink.tagId
would be a foreign key of tag.id
, so deleting the tag would trigger a cascade event deleting any promptTagLink
rows with the same tag id
You shouldn't need a trigger to accomplish that
looks like it's called DROP CASCADE
in postgresok great, cause i was using a trigger for my current method of storing the tagIds in array, but casdaing makes that part simpler
the docs on drizzle for cascade events aren't really there...
in mysql the drizzle schema is this, i would imagine the same for postgres:
sweet i'll give that a shoot
@angelelz @dane.stevens
And thank you both for taking the time to help, much appreciated!!
I am going to apply these lessons and work on getting the new structure with the intermediate table in place
forgot to give an update, was able to successfully refactor to using a many-to-many model
for any one who had a similar question to this post
here is the new tagPromptLinkTable and it's relations
and the update query:
Excellent, good job!
Thank you!
can we use inArray or
in
on manual query for deleted row ? case urlId
is an array uuid of string. This will reproduce error: invalid input syntax for type uuid
, btw i use sveltekit form & checkbox to do so.