How to sort by pagerequest with inner join

Question

I have two tables with many to one realtion. I want to sort data in table "user" by column "street" that is in "address" table in ASC or DESC direction defined by request param:

localhost:8080/getUsers?sort=address,desc

When I execute sql script:

SELECT * FROM user INNER JOIN address ON user.address_id=address.id ORDER BY street DESC

in workbench or phpMyAdmin it works good. All data are sorted by street name;

But when i try get it in postman by:

getUsers?sort=address,desc

I have this error in console output:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'INNER.address' in 'order clause'

Where is problem?

@Table(name = "user")
public class User {
    private Long id;
    private String name;
    @ManyToOne()
    @JoinColumn(name = "address_id")
    private Address address;
}

@Table(name = "address")
public class Address {
    private Long id;
    private String street
    @OneToMany(mappedBy = "address")
    private List<User> user;
}

public interface UserRepository extends JpaRepository<User, Long> {

     @Query(value = "SELECT * FROM user INNER JOIN address ON user.address_id=address.id",
        countQuery = "SELECT * FROM contact_messages INNER JOIN contact_topics ON contact_messages.contact_topic_id=contact_topics.id",
        nativeQuery = true)
     Page<User> findAll(Pageable pageable); 
}

Also when i wrote query in repository like this it works as good as in workbench:

@Query(value = "SELECT * FROM user INNER JOIN address ON user.address_id=address.id ORDER BY address DESC",
        countQuery = "SELECT * FROM contact_messages INNER JOIN contact_topics ON contact_messages.contact_topic_id=contact_topics.id ORDER BY address DESC",
        nativeQuery = true)

But i want to have controll of request and sort data when i want (using sort param).

Duplicate https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination — GnanaJeyam, Sep 09 '19 at 19:16

H Pat · Accepted Answer · 2019-09-09T18:57:52.210

0

Try replacing the SQL to SELECT u, a FROM user u INNER JOIN u.address a ORDER BY street DESC

edited Sep 09 '19 at 18:57

answered Sep 09 '19 at 17:46

H Pat

134
5

`Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode \-[IDENT] IdentNode: 'u' {originalText=u}` – John Sep 09 '19 at 18:37
this works: `SELECT u FROM user u INNER JOIN u.address a ORDER BY street DESC` is there a possibility to add `where address like '%:param%`; to this HQL ? – John Sep 09 '19 at 19:19
SELECT u, a.street FROM user u INNER JOIN u.address a WHERE a.street LIKE CONCAT('%',:param,'%')") ORDER BY a.street – H Pat Sep 09 '19 at 19:34

How to sort by pagerequest with inner join

1 Answers1