Filter sublists result in SQL query

I have a menu system where MenuTabs - the top level, they contain Menu and Menu contains MenuItem s.

Each level item has a list of user Role s it should be visible for.

I need to create a database query to select MenuTabs which contains only visible Menus with visible MenuItems for the current User. Is any way to do it with SQL query?

I use JPA and MySql, but any SQL query would be helpful.

As input data, I have a list of roles for the current user and I need to build right menu system for him/her. So user will see only allowed MenuTabs / Menus / MenuItems

Classes:

class MenuTab { 
   List<Menu> menu; 
   List<Role> userRoles;
}

class Menu {
   List<MenuItem> menuItems; 
   List<Role> userRoles;
}

class MenuItem {
   List<Role> userRoles;
}

class User {
   List<Role> userRoles;
}

Tables:

menutab            menutab_menu              menutab_role
  id                  menutabId                 menutabId
                      menuId                    roleId


menu               menu_menuitem             menu_role
  id                  menuId                    menuId
                      menuitemId                roleId


menuitem           menuitem_role
  id                  menuitemId
                      roleId


role
   id

user                user_role
   id                 userId
                      roleId

Answers


if you are using Hibernate you dont have to deal with sql i am not familiar with Hibernate but in NHibernate you could utilize HQL like that :

var _query = DetachedCriteria.For<MenuTab>()
                                .Add(Restrictions.In("userRoles", listOfRoles)
                                .Add(Restrictions.In("MenuItem.userRoles", listOfRoles);

In SQL I might perhaps do something like this:

SELECT
  ...
FROM menutab_menu tm
  INNER JOIN menu_menuitem mi ON tm.menuId     = mi.menuId
  INNER JOIN menutab t        ON tm.menutabId  = t.Id
  INNER JOIN menu m           ON tm.menuId     = m.Id
  INNER JOIN menuitem i       ON mi.menuitemId = i.Id
WHERE
  t.Id IN (
    SELECT tr.menutabId
    FROM menutab_role tr
      INNER JOIN user_role ur ON tr.roleId = ur.roleId
    WHERE ur.userId = @userId
  )
  AND m.Id IN (
    SELECT mr.menutabId
    FROM menu_role mr
      INNER JOIN user_role ur ON mr.roleId = ur.roleId
    WHERE ur.userId = @userId
  )
  AND i.Id IN (
    SELECT ir.menutabId
    FROM menuitem_role ir
      INNER JOIN user_role ur ON ir.roleId = ur.roleId
    WHERE ur.userId = @userId
  )

It is assumed that @userId is the current user ID, which is passed to the query as a parameter.


Need Your Help

Check if viewController push was animated or not

objective-c cocoa-touch

In my viewDidLoad method i would like to check how the viewController was pushed was it animated or not.

Issue with the Python Eve TokenAuth Feature

python mongodb flask eve

I am currently testing the python-eve library since a few days to create a restful API but I'm experiencing some issues when I follow this tutorial to implement a "Token Authentication".

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.