Dynamic SQL query with Pagination

Support
#1

Hi,

I am trying to create dynamic SQL query that supports paginations, like the one provided by JpaRepository - Page findAll(Specification spec, Pageable pageable).

The UI is on React, so only the Backend is on JMIX.

I am trying to create a filter that returns pageable result and total number of pages/elements:

This is the table:

@DbView
@JmixEntity
@Entity
@Table(name = "MY_TABLE")
public class MyTable {

    @Id
    @Column(name = "ID", nullable = false)
    private Long id;

    @Column(name = "TYPE_ID", nullable = false)
    private Long typeId;

    @Column(name = "TYPE_NAME", length = 1000)
    private String typeName;

    //---
}

I need to filter for typeId and typeName(and 10 other fields). The input parameters for the query are lists of strings. Both lists can be null.

With DataManager I have Pagination, BUT I do NOT have the total number of pages/elements:

    //---
    @Autowired
    private final DataManager dataManager;


    public Page<MyTable> getResult(List<String> typeIds, List<String> typeNames, int page, int size) {

        LogicalCondition logicalCondition = LogicalCondition.and();
        // -----
        
        if (CollectionUtils.isNotEmpty(typeIds)) {
            logicalCondition = logicalCondition.add(PropertyCondition.inList("typeId", typeIds));
        }
    
        if (CollectionUtils.isNotEmpty(typeNames)) {
            logicalCondition = logicalCondition.add(PropertyCondition.inList("typeName", typeNames));
        }

        List<MyTable> result = dataManager
            .load(MyTable.class).condition(logicalCondition)
            .firstResult((page - 1) * size)
            .maxResults(size)
            .sort(Sort.by(Sort.Direction.DESC, "id"))
            .list();
            
         // ----   
    }

So in order to get the number of pages I need to create a second query,

        Integer count = dataManager
                .loadValue("SELECT count(t.id) FROM MY_TABLE t WHERE ...."), Integer.class)
                .setParameters(parameters)
                .one();

The loadValue method of DataManager does not support condition, so I need to write the second query manually. And I do not want to have to 2 different queries for the pretty much same request - the only difference between the requests will be the paging and sorting.

I can build the query manually and use it for both requests, but it looks really bad.

        List<String> myConditions = new ArrayList<>();
        Map<String, Object> parameters = new HashMap<>();

        if (CollectionUtils.isNotEmpty(typeIds)) {
            myConditions.add("t.typeId IN :typeIds");
            parameters.put("typeIds", typeIds);
        }
        //......

Doing this for 10+ parameters is not good. And I want to get the data with a single request.

I tried using the JmixRepository like:

    @Repository
    public interface MyRepository extends JmixDataRepository<MyTable, Long> {

        @Query("SELECT t FROM MyTable t WHERE " +
                "(:typeIds IS NULL OR t.typeId IN :typeIds) " +
                "AND (:personNames IS NULL OR t.personName IN :personNames) ")
        Page<MyTable> findMeSomething(
                @Param(value = "typeIds") List<Long> typeIds,
                @Param(value = "personNames") List<String> personNames,
                Pageable pageable);
    }

But it does NOT work because IS NULL can not be used on objects and lists are objects. If the input parameters are primitive types it works as expected, but in my case I got lists, dates etc.

There must be a better way to do this.
Any ideas ?

Thanks,
Yordan

#2

Hello,

its also not elegant but you could maybe use something like this:
(COALESCE(:typeIds, '') = '' OR t.typeIds IN (:typeIds))
→ PreCondition: Collection typeIds is eithery null or has values (empty list should not work)

In general i would use CriteriaBuilder for that but this is not supported by Jmix EntitiyManager per Documentation. Maybe there is a safe option to unpack the general EntityManager and use its features.

Would also be interested for better solutions for this kind of problem :slight_smile:

#3

Hi,

I tested your suggestion and got strange error.

I am not sure that COALESCE can be used on input parameters.
Why select count and why boolean?

Method threw 'org.springframework.dao.InvalidDataAccessApiUsageException' exception.
You have attempted to set a value of type class java.util.ArrayList for parameter personNames with expected type of 
class java.lang.Boolean from query string SELECT count(t) FROM MyTable t WHERE 
(COALESCE( :typeIds, '') = '' OR t.typeId IN :typeIds) AND (COALESCE( :personNames, '') = '' OR t.personName IN :personNames)

The query:

@Repository
public interface MyRepo extends JmixDataRepository<MyTable, Long> {
     
    @Query("SELECT t FROM MyTable t WHERE " +
            "(COALESCE(:typeIds, '') = '' OR t.typeId IN :typeIds) " +
            "AND (COALESCE(:personNames, '') = '' OR t.personName IN :personNames)")
    Page<MyTable> findMeSomething2(
            @Param(value = "typeIds") List<Long> typeIds,
            @Param(value = "personNames") List<String> personNames,
            Pageable pageable);
}

Both lists are not null and not empty in this try.

I also tried ISNULL function, but no luck there as well.

    @Query("SELECT t FROM MyTable t WHERE " +
            "(ISNULL(:typeIds, '') = '' OR t.typeId IN :typeIds) " +
            "AND (ISNULL(:personNames, '') = '' OR t.personName IN :personNames)")
    Page<MyTable> findMeSomething3(
            @Param(value = "typeIds") List<Long> typeIds,
            @Param(value = "personNames") List<String> personNames,
            Pageable pageable);
#4

That’s an interesting problem.

Jmix apparently doesn’t have a convenient API for getting the total count for a query. But there is the DataManager.getCount(LoadContext) method which is used under the hood by some framework features. You can use it too as below:

LoadContext<User> loadContext = new LoadContext<>(metadata.getClass(User.class));
LoadContext.Query query = loadContext.setQueryString("select e from User e");
query.setCondition(PropertyCondition.equal("usename", "admin"));

long count = dataManager.getCount(loadContext);

query.setFirstResult(0);
query.setMaxResults(100);

List<User> list = dataManager.loadList(loadContext);

getCount() processes the LoadContext the same as loadList(), but replaces the entity e in the resulting query with count(e). So you create the LoadContext, execute getCount() to get the total number of records, than use the same LoadContext but with paging to load a single page.

This is not clear to me. How can you get the single page of rows and the total count together?

#5

Thank you!

This is exactly what I needed.