Is it possible to query both from entity and list object-CodePudding

I want to query from both the entity and a list object. Say I have an entity called "Customer", and I have a list of potential customers of object Potential.

Customer {
    id, name, address, ...
}

Potential {
    id, name, address, ...
}

In my repository I write the query as follows if I want to customize the query to get customers

@Query("SELECT c FROM Customer c WHERE c.status = :status")
List<Customer> findAllSpecialCustomers(String status)

But if I currently have a list object

List<Potential> potentials

And I want to include it in the Query above, how should I do it? Or is it even possible? The reason why I want to do this is because both entities represent different tables but i want to do sorting and pagination on the combined records of the two entities. Also, potentials is queried from graph database. While entity Customer is from mysql database.

Basically, I have a potentials list object that is queried from graph database. I want to union it with Customer entity from a mysql database through @Query and apply sorting and pagination to the combined records.

CodePudding user response：

Use a native union query and instead of using Customer or Potential, create another POJO class to map query results.

CodePudding user response：

I assume that there is some property in your Potential class that identifies a Customer. For argument's sake, let's assume that the id field in the two classes are the same, e.g., you want to pair up the Potential with id == 123 with the Customer with id == 123.

The simplest thing that I can think of is to map the list of Potentials to a list of Integers (or whatever type the id is), and then use that as a parameter to an "in" clause in your Customer query. For example,

@Query("SELECT c FROM Customer c WHERE c.id in :idList")
List<Customer> findCustomersById(List<int> idList)

and

findCustomersById(
 potentials
  .stream()
  .map(Potential::getId)
  .collect(Collectors.toList()
);

As far as "zipping" the two lists, i.e. pairing up the matches from the two lists, I'll leave that as an exercise for you :-)