I have multiple grids that display data based on a given filter (in a web application using a REST Api). The data structure displayed is always the same (to simplify the problem), but depending on the screen on which the user is, the displayed results are different.
In addition, and this is the issue, some results must be disabled so that the user cannot select them.
Example: A Foo has N Bars. If I want to add a new child (bar) to the father (foo), I go to the search screen, but I want the filtered grid shows as disabled children which are already related to the father.
Currently I'm controlling this issue on the server (database querys) by doing specifics joins depending on the scenario and "disabling" results I don't want. But this approach causes I cannot reuse queries (due to specifics joins. Maybe I need to search Bars int order relate them with other father Baz, and I want disable Bars that are already related with current father...)
Another approach could be the following:
Before starting to implement this solution I would like to know if there is any better option. I'm sure this is a recurring problem and I don't want to reinvent the wheel.
Any strategy or suggestion?
Edit: show example:
Assuming this model:
Category N:M Item SalesPromotion N:M Item
I have two different screens: one showing items belongs to one category and another showing items belongs to one sales promotion. In each screen I can search for items and add them to the Category or SalesPromotion. But when I'm searching items, I want items that already belongs to Category/SalesPromotion to show as disabled (or not shown, for simplicity in this example). I can do this in server, doing queries like these:
-- Query for search Items in Category screen SELECT * FROM ITEMS i LEFT JOIN ItemsCategories ic on ic.ItemId = i.ItemId WHERE ic.CategoryId IS NULL OR ic.CategoryId <> @CurrentCategoryId -- Query for search Items in SalesPromotion screen SELECT * FROM ITEMS i LEFT JOIN ItemsSalesPromotions isp on isp.ItemId= i.ItemId WHERE isp.PromotionId IS NULL OR isp.PromotionId <> @CurrentPromotionId
You can imagine what happens if I had more and more scenarios like these (with more complex model and queries of course).
One alternative could be:
So, my question is wheter this approach is a good solution or is there a well-known solution for this issue (I think so).
i changed my answer based on op's comment
so you basically have two options here.
LEFT JOIN part is useless. Your
WHERE clause only uses the
ITEMS table, so it has no impact on the set of rows returned, and since the other table has no corresponding row, the columns for this other table are all
You can thus have a single
SELECT * FROM ITEMS i (+ filter) and adjust your UI based on the
PromotionId, etc. columns being
©2020 All rights reserved.