mybatis中进行三目判断，不同sql结果累加union(尤其对于多种等效权限，资源的准备)

 

mybatis中进行三目判断，不同sql结果累加union(尤其对于多种等效权限，资源的准备) 

 

<sql id="selectProjectByUserBaseSql">

        SELECT p.*,

        IF(s.`id` is NULL, FALSE, TRUE) as 'isStar',

        u.`id` as 'createBy.id',

        IF(u.`name` is NULL, u.`username`, u.`name`) as 'createBy.username',

        u.`avatar` as 'createBy.avatar'

        from project p

        left join `user` u on u.`id` = p.`user_id`

        left join star s on (s.target_id = p.id and s.`target` = 'project' and s.user_id = #{userId})

        where p.id in (

        <!-- 当前用户是project的创建者或管理员 -->

        select DISTINCT p.id

        from project p

        left join rel_project_admin rpa on rpa.project_id = p.id

        where p.user_id = #{userId} or rpa.user_id = #{userId}

 

        union all

 

        <!-- 当前用户权限关联的project -->

        select DISTINCT p.id

        from project p

        left join rel_role_project rrp on rrp.project_id = p.id

        left join rel_role_user rru on rru.role_id = rrp.role_id

        where rru.user_id = #{userId}

 

 

        union all

 

        <!--当前用户是project所在org的owner-->

        select DISTINCT p.id

        from project p

        left join rel_user_organization ruo on ruo.org_id = p.org_id

        left join organization o on o.id = p.org_id

        where o.user_id = #{userId}

 

        union all

 

        <!--当前org 下project对普通成员可见-->

 

        select DISTINCT p.id

        from project p

        left join rel_user_organization ruo on ruo.org_id = p.org_id

        left join organization o on o.id = p.org_id

        where ruo.user_id = #{userId} and (ruo.role = 1 or (p.visibility = 1 and o.member_permission = 1))

        )

    </sql>