Is This SQL Query Efficient? Seeking Advice!
I'm quite beginner to SQL and Drizzle, and I wrote a query, but I have no idea how efficient it is. I don’t even know what to look for. I asked AI, and according to it, my query is inefficient because it contains a subquery that runs for every row in the database:
where: or(
eq(topping_categories.isGlobal, true),
inArray(
topping_categories.id,
adminDB
.select({ id: toppings_dishes_connect.toppingCategoryID })
.from(toppings_dishes_connect)
.where(eq(toppings_dishes_connect.dishID, dishId))
)
),
But if these functions exist, why shouldn’t they be used?
The main goal is that restaurants can add dishes, and each dish can belong to multiple dish groups, which I manage through a junction table. I want to fetch the associated dish groups as well as global ones that don’t need to be assigned to a dish because they apply to all dishes.
The image shows the full query and table structure.
What do you think about my approach? Thanks in advance for any help—I’d really appreciate any guidance!
1 Reply
Hi there!
You could use
.toSQL() to see your query (don't forget to print it). Also, I do not recommend touching any ORM before learning SQL. Try to use raw SQL queries or maybe just don't use "with" in your query and use .innerJoin, .leftJoin etc. Try to understand what you're doing.
As far I understood, you need to fetch dish groups id. So you need to select only id of the dish group, inner join it to dish, inner join dish to the restaurant and that's it. You will get array of id.