HQL query problems encountered in the project and HQL problems encountered in the project

HQL query problems encountered in the project and HQL problems encountered in the project

Problem description:
Purpose: To query all components of the latest version.
Description: component: version = 1: n. If this component has only one version, it must be able to be identified.

The project uses a memory database and cannot see the table structure. The example here is only used for simulation.
That is:

select component from Component component where component.owner=:userId andcomponent.componentId.version.versionString in (select Max(c.componentId.version.versionString) from Component c where component.owner=:userId  group by c.componentId.name )

Deficiency: I found that different versions of the same component can appear. I did not find this bug, but they later found it ..

4. Setbacks

select c2.id,c2.name,c2.user,c2.categoryname, Max(c2.version) version from component c2 where  c2.user="tan"  group by c2.name;

In this way, there is no problem in database query. The key is that the project is generally object-oriented. If it is changed to the following in the project:

select c.componentId.name,Max(c.componnetId.version.versionString) from component cwhere c.owner=:userId group by c.componentId.name

It can be displayed normally, but when other fields (such as c. image) It will report an error. If you want it to group without reporting an error, it is impossible to do so in actual situations, because the components are the same but the versions are different.

5. Final Solution
In the above object query, only one component name is used and the component of the corresponding version cannot be uniquely identified. How can this problem be uniquely identified?
After thinking for a long time, I did not think of a solution. Later, with the help of my team lead, I finally solved the problem.

Since a field cannot be uniquely identified, why not use two fields for unique determination?

CONCAT (s1, s2) connects to a string function jpqhql hql concat ([Object Attributes], [Object Attributes])

By using the CONCAT function, you can bind the component name and version to uniquely identify the latest version of the component.

The Code is as follows:

 select component from Component component where component.owner=:userId and CONCAT(component.componentId.name,component.componentId.version.versionString)  in (select CONCAT(c.componentId.name,Max(c.componentId.version.versionString)) from Component c group by c.componentId.name )

6. Reflection

When you encounter problems, you must dare to think about it, dare to think about it at different levels, and constantly try to solve it. Remember not to stick to your own opinions and stop at the original place, it is often the moment when the mind is crossed.