wsc20190102 commented on issue #24386:
URL:
https://github.com/apache/shardingsphere/issues/24386#issuecomment-1447990493
this is the sql with spring data jpa
`
@Query(nativeQuery = true,
value = "SELECT " +
"u.id AS id, " +
"u.create_date AS createDate, " +
"u.update_date AS updateDate, " +
"u.user_name AS userName, " +
"u.disable AS disable, " +
"u.email AS email, " +
"ent.org_name AS orgName, " +
"escrow.escrow_org_name AS escrowOrgName, " +
"GROUP_CONCAT(r.role_name) AS roleName "+
"FROM sys_user AS u " +
"LEFT JOIN nbms_ent AS ent " +
"ON u.id = ent.user_id AND ent.is_deleted = 0 " +
"LEFT JOIN nbms_escrow AS escrow " +
"ON u.escrow_id = escrow.id " +
"LEFT JOIN sys_user_role AS ur " +
"ON u.id = ur.user_id "+
"LEFT JOIN sys_role AS r " +
"ON ur.role_id = r.id "+
"WHERE if( :isEscrowSelected = 0, u.escrow_id IS NULL ,
u.escrow_id IS NOT NULL ) " +
"AND if( :roleId IS NOT NULL, ur.role_id = :roleId, 1=1
) "+
"AND if( :userName != '', u.user_name LIKE
CONCAT('%',:userName,'%'), 1=1 ) " +
"AND if( :orgName != '', ent.org_name LIKE
CONCAT('%',:orgName,'%'), 1=1 ) " +
"AND if( :escrowOrgName != '', escrow.escrow_org_name
LIKE CONCAT('%',:escrowOrgName,'%'), 1=1 ) " +
"AND if( :email != '', u.email = :email, 1=1 ) " +
"AND if( :beginDate IS NOT NULL, u.create_date >=
:beginDate, 1=1 ) " +
"AND if( :endDate IS NOT NULL, u.create_date <=
:endDate, 1=1 ) " +
"GROUP BY id,org_name,escrow_org_name " +
"ORDER BY createDate DESC ",
countQuery =
"SELECT count(*) " +
"FROM sys_user AS u " +
"LEFT JOIN nbms_ent AS ent " +
"ON u.id = ent.user_id AND ent.is_deleted = 0 " +
"LEFT JOIN nbms_escrow AS escrow " +
"ON u.escrow_id = escrow.id " +
"LEFT JOIN sys_user_role AS ur " +
"ON u.id = ur.user_id "+
"LEFT JOIN sys_role AS r " +
"ON ur.role_id = r.id "+
"WHERE if( :isEscrowSelected = 0, u.escrow_id IS
NULL , u.escrow_id IS NOT NULL ) " +
"AND if( :roleId IS NOT NULL, ur.role_id =
:roleId, 1=1 ) "+
"AND if( :userName != '', u.user_name LIKE
CONCAT('%',:userName,'%'), 1=1 ) " +
"AND if( :orgName != '', ent.org_name LIKE
CONCAT('%',:orgName,'%'), 1=1 ) " +
"AND if( :escrowOrgName != '',
escrow.escrow_org_name LIKE CONCAT('%',:escrowOrgName,'%'), 1=1 ) " +
"AND if( :email != '', u.email = :email, 1=1 ) "
+
"AND if( :beginDate IS NOT NULL, u.create_date
>= :beginDate, 1=1 ) " +
"AND if( :endDate IS NOT NULL, u.create_date <=
:endDate, 1=1 ) ")
Page<SysUserEscrowDTO> pageQueryAll(@Param("isEscrowSelected") Integer
isEscrowSelected,
@Param("userName") String userName,
@Param("roleId") Integer roleId,
@Param("orgName") String orgName,
@Param("escrowOrgName") String
escrowOrgName,
@Param("email") String email,
@Param("beginDate") Date beginDate,
@Param("endDate") Date endDate,
@Param("pageable") Pageable
pageable);
`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
