This is an automated email from the ASF dual-hosted git repository.
andreapatricelli pushed a commit to branch 3_0_X
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/3_0_X by this push:
new 051c91c497 [SYNCOPE-1906] Fixing order by on plain attribute (#1164)
051c91c497 is described below
commit 051c91c497449ea39e4c6d3df3b9a14e816f24c1
Author: Andrea Patricelli <[email protected]>
AuthorDate: Wed Sep 3 17:36:33 2025 +0200
[SYNCOPE-1906] Fixing order by on plain attribute (#1164)
---
.../persistence/jpa/dao/MyJPAJSONAnySearchDAO.java | 14 +++-
.../persistence/jpa/dao/OJPAJSONAnySearchDAO.java | 15 +++-
.../core/persistence/jpa/dao/JPAAnySearchDAO.java | 11 ++-
.../core/persistence/jpa/inner/AnySearchTest.java | 93 ++++++++++++++++++++++
.../apache/syncope/fit/core/UserIssuesITCase.java | 69 ++++++++++++++++
5 files changed, 194 insertions(+), 8 deletions(-)
diff --git
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
index ec86a34bdd..fe61bec605 100644
---
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
+++
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/MyJPAJSONAnySearchDAO.java
@@ -80,9 +80,17 @@ public class MyJPAJSONAnySearchDAO extends JPAAnySearchDAO {
obs.views.add(svs.field());
- item.select = svs.field().alias + '.'
- + (schema.isUniqueConstraint() ? "attrUniqueValue" :
key(schema.getType()))
- + " AS " + fieldName;
+ item.select = new StringBuilder().append("( SELECT usa").
+ append('.').
+ append((schema.isUniqueConstraint() ? "attrUniqueValue" :
key(schema.getType()))).
+ append(" FROM ").
+ append(svs.field().name).
+ append(" usa WHERE usa.any_id = ").
+ append(svs.field().alias).
+ append(".any_id").
+ append(" AND usa.plainSchema
='").append(fieldName).append("'").
+ append(" LIMIT 1").
+ append(") AS ").append(fieldName).toString();
item.where = "plainSchema = '" + fieldName + '\'';
item.orderBy = fieldName + ' ' + clause.getDirection().name();
}
diff --git
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OJPAJSONAnySearchDAO.java
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OJPAJSONAnySearchDAO.java
index d08a4acb42..7bb31c86b9 100644
---
a/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OJPAJSONAnySearchDAO.java
+++
b/core/persistence-jpa-json/src/main/java/org/apache/syncope/core/persistence/jpa/dao/OJPAJSONAnySearchDAO.java
@@ -77,9 +77,18 @@ public class OJPAJSONAnySearchDAO extends JPAAnySearchDAO {
obs.views.add(svs.field());
- item.select = svs.field().alias + '.'
- + (schema.isUniqueConstraint() ? "u" : "") +
key(schema.getType())
- + " AS " + fieldName;
+ item.select = new StringBuilder().append("( SELECT usa").
+ append('.').
+ append((schema.isUniqueConstraint() ? "u" : "") +
key(schema.getType())).
+ append(" FROM ").
+ append(svs.field().name).
+ append(" usa WHERE usa.any_id = ").
+ append(svs.field().alias).
+ append(".any_id").
+ append(" AND usa.plainSchema
='").append(fieldName).append("'").
+ append(" FETCH FIRST 1 ROWS ONLY ").
+ append(") AS ").append(fieldName).toString();
+
item.where = "plainSchema = '" + fieldName + '\'';
item.orderBy = fieldName + ' ' + clause.getDirection().name();
}
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java
index 1c11721cbb..5ec0e795b7 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java
@@ -1006,8 +1006,15 @@ public class JPAAnySearchDAO extends
AbstractAnySearchDAO {
obs.views.add(svs.asSearchViewSupport().attr());
item.select = new StringBuilder().
-
append(svs.asSearchViewSupport().attr().alias).append('.').append(key(schema.getType())).
- append(" AS ").append(fieldName).toString();
+ append("( SELECT
usa").append('.').append(key(schema.getType())).
+ append(" FROM ").
+ append(svs.asSearchViewSupport().attr().name).
+ append(" usa WHERE usa.any_id = ").
+ append(svs.asSearchViewSupport().attr().alias).
+ append(".any_id").
+ append(" AND usa.schema_id
='").append(fieldName).append("'").
+ append(isOracle() ? " FETCH FIRST 1 ROWS ONLY " : " LIMIT
1").
+ append(") AS ").append(fieldName).toString();
item.where = new StringBuilder().
append(svs.asSearchViewSupport().attr().alias).
append(".schema_id='").append(fieldName).append("'").toString();
diff --git
a/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java
b/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java
index 5218d66697..5f917efca4 100644
---
a/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java
+++
b/core/persistence-jpa/src/test/java/org/apache/syncope/core/persistence/jpa/inner/AnySearchTest.java
@@ -36,10 +36,12 @@ import java.util.stream.Stream;
import org.apache.syncope.common.lib.SyncopeConstants;
import org.apache.syncope.common.lib.types.AnyTypeKind;
import org.apache.syncope.common.lib.types.IdRepoEntitlement;
+import
org.apache.syncope.core.persistence.api.attrvalue.validation.PlainAttrValidationManager;
import org.apache.syncope.core.persistence.api.dao.AnyObjectDAO;
import org.apache.syncope.core.persistence.api.dao.AnySearchDAO;
import org.apache.syncope.core.persistence.api.dao.AnyTypeDAO;
import org.apache.syncope.core.persistence.api.dao.GroupDAO;
+import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
import org.apache.syncope.core.persistence.api.dao.RealmDAO;
import org.apache.syncope.core.persistence.api.dao.RoleDAO;
import org.apache.syncope.core.persistence.api.dao.UserDAO;
@@ -56,6 +58,8 @@ import
org.apache.syncope.core.persistence.api.dao.search.ResourceCond;
import org.apache.syncope.core.persistence.api.dao.search.RoleCond;
import org.apache.syncope.core.persistence.api.dao.search.SearchCond;
import org.apache.syncope.core.persistence.api.entity.AnyType;
+import org.apache.syncope.core.persistence.api.entity.EntityFactory;
+import org.apache.syncope.core.persistence.api.entity.PlainSchema;
import org.apache.syncope.core.persistence.api.entity.anyobject.AMembership;
import org.apache.syncope.core.persistence.api.entity.anyobject.AnyObject;
import org.apache.syncope.core.persistence.api.entity.group.Group;
@@ -100,6 +104,15 @@ public class AnySearchTest extends AbstractTest {
@Autowired
private RoleDAO roleDAO;
+
+ @Autowired
+ private PlainSchemaDAO plainSchemaDAO;
+
+ @Autowired
+ private EntityFactory entityFactory;
+
+ @Autowired
+ private PlainAttrValidationManager validator;
@BeforeEach
public void adjustLoginDateForLocalSystem() throws ParseException {
@@ -973,4 +986,84 @@ public class AnySearchTest extends AbstractTest {
assertNotNull(users);
assertEquals(4, users.size());
}
+
+ @Test
+ public void issueSYNCOPE1906() {
+ User bellini = userDAO.findByUsername("bellini");
+ assertNotNull(bellini);
+
+ PlainSchema ctypeSchema = plainSchemaDAO.find("ctype");
+ assertNotNull(ctypeSchema);
+
+ userDAO.save(addPlainAttr(bellini, ctypeSchema, "aa1"));
+
+ User puccini = userDAO.findByUsername("puccini");
+ assertNotNull(bellini);
+
+ userDAO.save(addPlainAttr(puccini, ctypeSchema, "aa2"));
+
+ User verdi = userDAO.findByUsername("verdi");
+ assertNotNull(verdi);
+
+ userDAO.save(addPlainAttr(verdi, ctypeSchema, "aa3"));
+
+ User vivaldi = userDAO.findByUsername("vivaldi");
+ assertNotNull(vivaldi);
+
+ userDAO.save(addPlainAttr(vivaldi, ctypeSchema, "aa4"));
+
+ User rossini = userDAO.findByUsername("rossini");
+ assertNotNull(rossini);
+
+ userDAO.save(addPlainAttr(rossini, ctypeSchema, "aa5"));
+
+ entityManager().flush();
+
+ OrderByClause orderByCtype = new OrderByClause();
+ orderByCtype.setField("ctype");
+ orderByCtype.setDirection(OrderByClause.Direction.DESC);
+
+ OrderByClause orderByFirstname = new OrderByClause();
+ orderByFirstname.setField("username");
+ orderByFirstname.setDirection(OrderByClause.Direction.DESC);
+
+ AnyCond idCond = new AnyCond(AttrCond.Type.ISNOTNULL);
+ idCond.setSchema("id");
+
+ List<User> users =
+ searchDAO.search(SearchCond.getLeaf(idCond),
List.of(orderByCtype, orderByFirstname), AnyTypeKind.USER);
+
+ assertEquals("bellini", users.get(4).getUsername());
+ assertEquals("puccini", users.get(3).getUsername());
+ assertEquals("verdi", users.get(2).getUsername());
+ assertEquals("vivaldi", users.get(1).getUsername());
+ assertEquals("rossini", users.get(0).getUsername());
+
+ // order by ctype even not searching by it
+ AttrCond surnameCond = new AttrCond(AttrCond.Type.ILIKE);
+ surnameCond.setSchema("surname");
+ surnameCond.setExpression("%ini");
+
+ AttrCond coolCond = new AttrCond(AttrCond.Type.ISNULL);
+ coolCond.setSchema("cool");
+
+ users =
searchDAO.search(SearchCond.getAnd(SearchCond.getLeaf(surnameCond),
SearchCond.getLeaf(coolCond)),
+ List.of(orderByCtype), AnyTypeKind.USER);
+
+ assertFalse(users.isEmpty());
+ assertEquals(2, users.size());
+ }
+
+ private User addPlainAttr(final User user, final PlainSchema plainSchema,
final String value) {
+ user.getPlainAttr(plainSchema.getKey())
+ .ifPresentOrElse(ctype ->
ctype.getValues().get(0).setStringValue(value), () -> {
+ UPlainAttr attr =
entityFactory.newEntity(UPlainAttr.class);
+ attr.setOwner(user);
+ attr.setSchema(plainSchema);
+ attr.add(validator, value,
anyUtilsFactory.getInstance(AnyTypeKind.USER));
+
+ user.add(attr);
+ });
+ return user;
+ }
}
diff --git
a/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
b/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
index ea9fc0a6a1..79d23ba03c 100644
---
a/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
+++
b/fit/core-reference/src/test/java/org/apache/syncope/fit/core/UserIssuesITCase.java
@@ -104,6 +104,7 @@ import org.apache.syncope.common.lib.types.StatusRType;
import org.apache.syncope.common.lib.types.TaskType;
import org.apache.syncope.common.lib.types.UnmatchingRule;
import org.apache.syncope.common.rest.api.RESTHeaders;
+import org.apache.syncope.common.rest.api.beans.AnyQuery;
import org.apache.syncope.common.rest.api.beans.RealmQuery;
import org.apache.syncope.common.rest.api.beans.ReconQuery;
import org.apache.syncope.common.rest.api.beans.TaskQuery;
@@ -1923,4 +1924,72 @@ public class UserIssuesITCase extends AbstractITCase {
SCHEMA_SERVICE.delete(SchemaType.PLAIN,
userWithDotSchema.getKey());
}
}
+
+ @Test
+ public void issueSYNCOPE1906() {
+ UserCR userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa1"));
+ UserTO aa1 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa2"));
+ UserTO aa2 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa3"));
+ UserTO aa3 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa4"));
+ UserTO aa4 = createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "aa5"));
+ UserTO aa5 = createUser(userCR).getEntity();
+
+ // add also other users with valued ctype
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "a ctype"));
+ createUser(userCR).getEntity();
+
+ userCR =
UserITCase.getUniqueSample("[email protected]");
+ userCR.getPlainAttrs().removeIf(attr ->
"ctype".equals(attr.getSchema()));
+ userCR.getPlainAttrs().add(attr("ctype", "a ctype 2"));
+ createUser(userCR).getEntity();
+ try {
+ await().until(() -> USER_SERVICE.search(new
AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("ctype").equalTo("aa*").query())
+ .size(0)
+ .page(1)
+ .orderBy("ctype DESC")
+ .build()).getTotalCount() == 5);
+ List<UserTO> users = USER_SERVICE.search(new
AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("ctype").equalTo("aa*").query())
+ .size(10)
+ .page(1)
+ .orderBy("ctype DESC")
+ .build()).getResult();
+
+ assertEquals(5, users.size());
+
+ assertEquals(aa1.getUsername(), users.get(4).getUsername());
+ assertEquals(aa2.getUsername(), users.get(3).getUsername());
+ assertEquals(aa3.getUsername(), users.get(2).getUsername());
+ assertEquals(aa4.getUsername(), users.get(1).getUsername());
+ assertEquals(aa5.getUsername(), users.get(0).getUsername());
+ } finally {
+ USER_SERVICE.search(new AnyQuery.Builder().fiql(
+
SyncopeClient.getUserSearchConditionBuilder().is("ctype").equalTo("aa*").query())
+ .size(10)
+ .page(1)
+ .orderBy("ctype DESC")
+ .build()).getResult().forEach(u -> deleteUser(u.getKey()));
+ }
+ }
}
