Hi everyone,
I am facing a problem that I am not able to solve, thus I am asking this forum. I hope you can help me out of this:

I am havong a pretty simple scenario: I have products that are linked to a category. Each product can only be assigned to one category, thus we are talking about a 1:n relationship. While categories are intended for being used all users, the products are assigned to the users. Here again we have a 1:n relationship.

My problem is that I want to retrieve all categories and their linked products that belong to a certain user. In detail, I want to retrieve all the categories even though they might have no products assigned to them (for this specific user). In addition, I only want to retrieve the products for the specific user.

Is there a way to formulate a query that return the categories in first place with the desired products linked to them?

Just to keep no important information:
- Category has a list property pointing to the products. The query works fine except that it return too much rows, namely those of different users.

- Product has a property pointing to a Category
- Product has a property that point to the user which "owns" this Product

Could anybody tell me what is the best solution to solve this problem.

Many thanks in advance,
Florian

Unfortunately, there's no way to do this in a single query. If querying from the category end, in order to filter on the user you have to join with the product table at least. Once you add a where clause on the user id, any rows that don't match will be filtered out so categories with no products managed by the user will not appear in the results set.

This would be easy to handle in java if you add a method to get the products for a given user in your category class. The downside would be loading the entire product set for every category.