def get(cls, group_id, db_session=None):
     """ get group by primary key from session """
     if not group_id:
         return None
     db_session = get_db_session(db_session)
     query = db_session.query(cls.model)
     return query.get(group_id)

    def from_resource_deeper(cls, resource_id=None, limit_depth=1000000,
                             db_session=None, *args, **kwargs):
        """
        This returns you subtree of ordered objects relative
        to the start resource_id (currently only implemented in postgresql)

        :param resource_id:
        :param limit_depth:
        :param db_session:
        :return:
        """
        tablename = cls.model.__table__.name
        raw_q = """
            WITH RECURSIVE subtree AS (
                    SELECT res.*, 1 AS depth, res.ordering::CHARACTER VARYING AS sorting,
                    res.resource_id::CHARACTER VARYING AS path
                    FROM {tablename} AS res WHERE res.resource_id = :resource_id
                  UNION ALL
                    SELECT res_u.*, depth+1 AS depth,
                    (st.sorting::CHARACTER VARYING || '/' || res_u.ordering::CHARACTER VARYING ) AS sorting,
                    (st.path::CHARACTER VARYING || '/' || res_u.resource_id::CHARACTER VARYING ) AS path
                    FROM {tablename} res_u, subtree st
                    WHERE res_u.parent_id = st.resource_id
            )
            SELECT * FROM subtree WHERE depth<=:depth ORDER BY sorting;
        """.format(tablename=tablename)
        db_session = get_db_session(db_session)
        text_obj = sa.text(raw_q)
        query = db_session.query(cls.model, 'depth', 'sorting', 'path')
        query = query.from_statement(text_obj)
        query = query.params(resource_id=resource_id, depth=limit_depth)
        return query

    def check_node_parent(cls, resource_id, new_parent_id, db_session=None,
                          *args, **kwargs):
        """
        Checks if parent destination is valid for node

        :param resource_id:
        :param new_parent_id:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session)
        new_parent = ResourceService.lock_resource_for_update(
            resource_id=new_parent_id,
            db_session=db_session)
        # we are not moving to "root" so parent should be found
        if not new_parent and new_parent_id is not None:
            raise ZigguratResourceTreeMissingException(
                'New parent node not found')
        else:
            result = cls.path_upper(new_parent_id,
                                    db_session=db_session)
            path_ids = [r.resource_id for r in result]
            if resource_id in path_ids:
                raise ZigguratResourceTreePathException(
                    'Trying to insert node into itself')

 def get(cls, entry_id, db_session=None):
     """ get entry by primary key from session """
     if not entry_id:
         return None
     db_session = get_db_session(db_session)
     query = db_session.query(cls.model)
     return query.get(entry_id)

    def path_upper(cls, object_id, limit_depth=1000000, db_session=None, *args,
                   **kwargs):
        """
        This returns you path to root node starting from object_id
            currently only for postgresql

        :param object_id:
        :param limit_depth:
        :param db_session:
        :return:
        """
        tablename = cls.model.__table__.name
        raw_q = """
            WITH RECURSIVE subtree AS (
                    SELECT res.*, 1 as depth FROM {tablename} res
                    WHERE res.resource_id = :resource_id
                  UNION ALL
                    SELECT res_u.*, depth+1 as depth
                    FROM {tablename} res_u, subtree st
                    WHERE res_u.resource_id = st.parent_id
            )
            SELECT * FROM subtree WHERE depth<=:depth;
        """.format(tablename=tablename)
        db_session = get_db_session(db_session)
        q = db_session.query(cls.model).from_statement(sa.text(raw_q)).params(
            resource_id=object_id, depth=limit_depth)
        return q

    def direct_perms_for_user(cls, instance, user, db_session=None):
        """
        returns permissions that given user has for this resource
            without ones inherited from groups that user belongs to

        :param instance:
        :param user:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session, instance)
        query = db_session.query(
            cls.models_proxy.UserResourcePermission.user_id,
            cls.models_proxy.UserResourcePermission.perm_name)
        query = query.filter(cls.models_proxy.UserResourcePermission.user_id ==
                             user.id)
        query = query.filter(
            cls.models_proxy.UserResourcePermission.resource_id ==
            instance.resource_id)

        perms = [PermissionTuple(user,
                                 row.perm_name,
                                 'user',
                                 None,
                                 instance, False, True) for row in query]

        # include all perms if user is the owner of this resource
        if instance.owner_user_id == user.id:
            perms.append(PermissionTuple(user, ALL_PERMISSIONS, 'user',
                                         None, instance, True))
        return perms

    def groups_for_perm(cls, instance, perm_name, group_ids=None,
                        limit_group_permissions=False,
                        db_session=None):
        """
        return PermissionTuples for groups that have given
        permission for the resource, perm_name is __any_permission__ then
        users with any permission will be listed

        :param instance:
        :param perm_name:
        :param group_ids: limits the permissions to specific group ids
        :param limit_group_permissions: should be used if we do not want to have
        user objects returned for group permissions, this might cause performance
        issues for big groups
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session, instance)
        group_perms = resource_permissions_for_users(cls.models_proxy,
                                                     [perm_name],
                                                     [instance.resource_id],
                                                     group_ids=group_ids,
                                                     limit_group_permissions=limit_group_permissions,
                                                     skip_user_perms=True,
                                                     db_session=db_session)
        if instance.owner_group_id:
            for user in instance.owner_group.users:
                group_perms.append(
                    PermissionTuple(user, ALL_PERMISSIONS, 'group',
                                    instance.owner_group, instance, True,
                                    True))

        return group_perms

    def users_for_perms(cls, perm_names, db_session=None):
        """
        return users hat have one of given permissions

        :param perm_names:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session)
        query = db_session.query(cls.model)
        query = query.filter(
            cls.models_proxy.User.id == cls.models_proxy.UserGroup.user_id
        )
        query = query.filter(
            cls.models_proxy.UserGroup.group_id
            == cls.models_proxy.GroupPermission.group_id
        )
        query = query.filter(cls.models_proxy.GroupPermission.perm_name.in_(perm_names))

        query2 = db_session.query(cls.model)
        query2 = query2.filter(
            cls.models_proxy.User.id == cls.models_proxy.UserPermission.user_id
        )
        query2 = query2.filter(
            cls.models_proxy.UserPermission.perm_name.in_(perm_names)
        )
        users = query.union(query2).order_by(cls.model.id)
        return users

 def get_paginator(
     cls,
     page=1,
     item_count=None,
     items_per_page=50,
     db_session=None,
     filter_params=None,
     **kwargs
 ):
     """ returns paginator over users belonging to the group"""
     if filter_params is None:
         filter_params = {}
     db_session = get_db_session(db_session)
     query = db_session.query(User)
     user_name_like = filter_params.get("user_name_like")
     if user_name_like:
         query = query.filter(User.user_name.like(user_name_like + "%"))
     query = query.order_by(User.id)
     return SqlalchemyOrmPage(
         query,
         page=page,
         item_count=item_count,
         items_per_page=items_per_page,
         **kwargs
     )

    def group_perms_for_user(cls, instance, user, db_session=None):
        """
        returns permissions that given user has for this resource
            that are inherited from groups

        :param instance:
        :param user:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session, instance)
        perms = resource_permissions_for_users(cls.models_proxy,
                                               ANY_PERMISSION,
                                               resource_ids=[
                                                   instance.resource_id],
                                               user_ids=[user.id],
                                               db_session=db_session)
        perms = [p for p in perms if p.type == 'group']
        # include all perms if user is the owner of this resource
        groups_dict = dict([(g.id, g) for g in user.groups])
        if instance.owner_group_id in groups_dict:
            perms.append(PermissionTuple(user, ALL_PERMISSIONS, 'group',
                                         groups_dict.get(
                                             instance.owner_group_id),
                                         instance, True, True))
        return perms

    def delete_branch(cls, resource_id=None, db_session=None, *args, **kwargs):
        """
        This deletes whole branch with children starting from resource_id

        :param resource_id:
        :param db_session:
        :return:
        """
        tablename = cls.model.__table__.name

        # lets lock rows to prevent bad tree states
        resource = ResourceService.lock_resource_for_update(
            resource_id=resource_id,
            db_session=db_session)
        parent_id = resource.parent_id
        ordering = resource.ordering
        raw_q = """
            WITH RECURSIVE subtree AS (
                    SELECT res.resource_id
                    FROM {tablename} AS res WHERE res.resource_id = :resource_id
                  UNION ALL
                    SELECT res_u.resource_id
                    FROM {tablename} res_u, subtree st
                    WHERE res_u.parent_id = st.resource_id
            )
            DELETE FROM resources where resource_id in (select * from subtree);
        """.format(tablename=tablename)
        db_session = get_db_session(db_session)
        text_obj = sa.text(raw_q)
        db_session.execute(text_obj, params={'resource_id': resource_id})
        cls.shift_ordering_down(parent_id, ordering, db_session=db_session)
        return True

    def by_group_name(cls, group_name, db_session=None):
        """ fetch group by name

        .. deprecated:: 0.8

        """
        db_session = get_db_session(db_session)
        return GroupService.by_group_name(group_name=group_name,
                                          db_session=db_session)

    def permissions(self):
        """

        .. deprecated:: 0.8

        :return:
        """
        db_session = get_db_session(None, self)
        return UserService.permissions(self, db_session=db_session)

    def base_query(cls, db_session=None):
        """
        returns base query for specific service

        :param db_session:
        :return: query
        """
        db_session = get_db_session(db_session)
        return db_session.query(cls.model)

    def perms_for_user(cls, instance, user, db_session=None):
        """
        returns all permissions that given user has for this resource
            from groups and directly set ones too

        :param instance:
        :param user:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session, instance)
        query = db_session.query(
            cls.models_proxy.GroupResourcePermission.group_id.label(
                'owner_id'),
            cls.models_proxy.GroupResourcePermission.perm_name,
            sa.literal('group').label('type'))
        query = query.filter(
            cls.models_proxy.GroupResourcePermission.group_id.in_(
                [gr.id for gr in user.groups]
            )
        )
        query = query.filter(
            cls.models_proxy.GroupResourcePermission.resource_id ==
            instance.resource_id)

        query2 = db_session.query(
            cls.models_proxy.UserResourcePermission.user_id.label('owner_id'),
            cls.models_proxy.UserResourcePermission.perm_name,
            sa.literal('user').label('type'))
        query2 = query2.filter(
            cls.models_proxy.UserResourcePermission.user_id ==
            user.id)
        query2 = query2.filter(
            cls.models_proxy.UserResourcePermission.resource_id ==
            instance.resource_id)
        query = query.union(query2)

        groups_dict = dict([(g.id, g) for g in user.groups])
        perms = [PermissionTuple(user,
                                 row.perm_name,
                                 row.type,
                                 groups_dict.get(row.owner_id) if
                                 row.type == 'group' else None,
                                 instance, False, True) for row in query]

        # include all perms if user is the owner of this resource
        if instance.owner_user_id == user.id:
            perms.append(PermissionTuple(user, ALL_PERMISSIONS, 'user',
                                         None, instance, True, True))
        groups_dict = dict([(g.id, g) for g in user.groups])
        if instance.owner_group_id in groups_dict:
            perms.append(PermissionTuple(user, ALL_PERMISSIONS, 'group',
                                         groups_dict.get(
                                             instance.owner_group_id),
                                         instance, True, True))

        return perms

 def by_resource_name(name,db_session=None):
     db_session = get_db_session(db_session)
     try:
         resource = db_session.query(Resource).filter(Resource.resource_name==name).one()
         log.debug(resource)
         return ResourceService.by_resource_id(resource_id=resource.resource_id,
                                               db_session=db_session)
     except Exception as e:
         log.debug('Error retrieving resource by name, {0}'.format(e))
         raise

    def by_group_name(cls, group_name, db_session=None):
        """
        fetch group by name

        :param group_name:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session)
        query = db_session.query(cls.model).filter(cls.model.group_name == group_name)
        return query.first()

    def get(cls, resource_id, db_session=None):
        """
        Fetch row using primary key -
        will use existing object in session if already present

        :param resource_id:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session)
        return db_session.query(cls.model).get(resource_id)

    def by_id(cls, user_id, db_session=None):
        """

        .. deprecated:: 0.8

        :param user_id:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session)
        return UserService.by_id(user_id=user_id, db_session=db_session)

    def get(cls, user_id, perm_name, db_session=None):
        """
        Fetch row using primary key -
        will use existing object in session if already present

        :param user_id:
        :param perm_name:
        :param db_session:
        :return:
        """
        db_session = get_db_session(db_session)
        return db_session.query(cls.model).get([user_id, perm_name])