How to sort by pagerequest with inner join

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).

Answer

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

Advertisement

Answer