How to disable elements in a grid

Question

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:

• Save the children (only ids) related to the father in an array in memory (javascript)
• In the "prerender" grid event (or similar) check for each element whether it is contained in the previous array or not (search by id). If so, mark it as disabled (for example). This is a reusable solution in client-side and I can always reuse the same query in server side.

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:

• Store items that already belongs to current Category/SalesPromotion in memory (javascript, clientside).
• On grid prerender event (or equivalent) in clientside determine what items must be disabled (by searching each row in stored items).

So, my question is wheter this approach is a good solution or is there a well-known solution for this issue (I think so).

Answer 1

i changed my answer based on op's comment

so you basically have two options here.

1. remove the Ids server side after you query the db. (affects response performance but safer).
2. do it on client side with js and remove them from the grid.

Answer 2

In your SELECTs, the 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 NULL.

You can thus have a single SELECT * FROM ITEMS i (+ filter) and adjust your UI based on the CategoryId, PromotionId, etc. columns being NULL.