org.hibernate.QueryException: illegal attempt to dereference collection in order by clause-CodePudding

I am trying to implement a simple HQL query of all objects of type A ordered by the following predicate :

a.getListB().get(0).getC().getLastname()

I tried the following HQL query :

select a_ from A a_ order by a_.listB.c.lastname

But I am getting the following exception :

org.hibernate.QueryException: illegal attempt to dereference collection

I have tried the following SQL query but I am getting inconsistent results :

select a.* from A a
left outer join B b on b.a_id=a.id
left outer join C c on b.uploaded_from=c.id
order by c.lastname=(select c_.lastname from A a_ 
left outer join B b_ on b_.a_id=a_.id
left outer join C c_ on b_.uploaded_from=c_.id 
where a.id=a_.id limit 1) asc;

Code snipet :

@Entity
@Table(name = "A")
pubic class A {
  private int id;
  private List<B> listB;

  @Id
  @GeneratedValue(strategy = IDENTITY)
  @Column(name = "id", unique = true, nullable = false)
  public Integer getId() {
    return this.id;
  }

  @OrderBy(clause = "id")
  @OneToMany(fetch = FetchType.LAZY, mappedBy = "a")
  public List<B> getListB() {
    return this.listB;
  }
}

@Entity
@Table(name = "B")
pubic class B {
  private int id;
  private A a;
  private C c;

  @Id
  @GeneratedValue(strategy = IDENTITY)
  @Column(name = "id", unique = true, nullable = false)
  public Integer getId() {
    return this.id;
  }

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "a_id", nullable = false)
  public A getA() {
    return this.a;
  }
  
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "uploaded_from", nullable = false)
  public C getC() {
    return this.c;
  }
}

@Entity
@Table(name = "C")
pubic class C {
  private int id;
  private String lastname;

  @Id
  @GeneratedValue(strategy = IDENTITY)
  @Column(name = "id", unique = true, nullable = false)
  public Integer getId() {
    return this.id;
  }

  @Column(name = "lastname")
  public String getLastname() {
     return this.lastname;
  }
}

Any hints how can I work around this problem please either in HQL, Criteria or even native SQL?

CodePudding user response：

I used the following tables to test one possible solution for your problem further below

create table A (
    id UUID
);

create table B ( 
    id UUID,
    id_a UUID,
    id_c UUID
);

create table C (
    id UUID,
    lastname varchar(63)
);

insert into A values 
('aaaaaaaa-aaaa-aaaa-aaaa-000000000000'),
('aaaaaaaa-aaaa-aaaa-aaaa-000000000001'),
('aaaaaaaa-aaaa-aaaa-aaaa-000000000002'),
('aaaaaaaa-aaaa-aaaa-aaaa-000000000003'),
('aaaaaaaa-aaaa-aaaa-aaaa-000000000004');

insert into C values 
('cccccccc-cccc-cccc-cccc-000000000000', 'C zero'),
('cccccccc-cccc-cccc-cccc-000000000001', 'C one'),
('cccccccc-cccc-cccc-cccc-000000000002', 'C two'),
('cccccccc-cccc-cccc-cccc-000000000003', 'C three'),
('cccccccc-cccc-cccc-cccc-000000000004', 'C four'),
('cccccccc-cccc-cccc-cccc-000000000005', 'C five'),
('cccccccc-cccc-cccc-cccc-000000000006', 'C six');

insert into B values 
('bbbbbbbb-bbbb-bbbb-bbbb-000000000000', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000000', 'cccccccc-cccc-cccc-cccc-000000000000'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000000', 'cccccccc-cccc-cccc-cccc-000000000001'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000000', 'cccccccc-cccc-cccc-cccc-000000000002'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000003', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000001', 'cccccccc-cccc-cccc-cccc-000000000003'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000004', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000001', 'cccccccc-cccc-cccc-cccc-000000000004'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000005', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000001', 'cccccccc-cccc-cccc-cccc-000000000005'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000006', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000002', 'cccccccc-cccc-cccc-cccc-000000000006'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000007', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000002', 'cccccccc-cccc-cccc-cccc-000000000000'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000008', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000002', 'cccccccc-cccc-cccc-cccc-000000000001'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000009', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000003', 'cccccccc-cccc-cccc-cccc-000000000002'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000010', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000003', 'cccccccc-cccc-cccc-cccc-000000000003'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000011', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000003', 'cccccccc-cccc-cccc-cccc-000000000004'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000012', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000004', 'cccccccc-cccc-cccc-cccc-000000000005'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000013', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000004', 'cccccccc-cccc-cccc-cccc-000000000006'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000014', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000004', 'cccccccc-cccc-cccc-cccc-000000000000'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000015', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000000', 'cccccccc-cccc-cccc-cccc-000000000001'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000016', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000001', 'cccccccc-cccc-cccc-cccc-000000000002'),
('bbbbbbbb-bbbb-bbbb-bbbb-000000000017', 'aaaaaaaa-aaaa-aaaa-aaaa-000000000002', 'cccccccc-cccc-cccc-cccc-000000000003');

Unique id_a entries are numbered via this intermediate select:

select id,id_a,id_c,ROW_NUMBER() 
  over (partition by id_a order by id_a) as rowNumber 
  from B as aggregate;

Result:

                  id                  |                 id_a                 |                 id_c                 | rownumber 
-------------------------------------- -------------------------------------- -------------------------------------- -----------
 bbbbbbbb-bbbb-bbbb-bbbb-000000000000 | aaaaaaaa-aaaa-aaaa-aaaa-000000000000 | cccccccc-cccc-cccc-cccc-000000000000 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000015 | aaaaaaaa-aaaa-aaaa-aaaa-000000000000 | cccccccc-cccc-cccc-cccc-000000000001 |         2
 bbbbbbbb-bbbb-bbbb-bbbb-000000000001 | aaaaaaaa-aaaa-aaaa-aaaa-000000000000 | cccccccc-cccc-cccc-cccc-000000000001 |         3
 bbbbbbbb-bbbb-bbbb-bbbb-000000000002 | aaaaaaaa-aaaa-aaaa-aaaa-000000000000 | cccccccc-cccc-cccc-cccc-000000000002 |         4
 bbbbbbbb-bbbb-bbbb-bbbb-000000000004 | aaaaaaaa-aaaa-aaaa-aaaa-000000000001 | cccccccc-cccc-cccc-cccc-000000000004 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000016 | aaaaaaaa-aaaa-aaaa-aaaa-000000000001 | cccccccc-cccc-cccc-cccc-000000000002 |         2
 bbbbbbbb-bbbb-bbbb-bbbb-000000000003 | aaaaaaaa-aaaa-aaaa-aaaa-000000000001 | cccccccc-cccc-cccc-cccc-000000000003 |         3
 bbbbbbbb-bbbb-bbbb-bbbb-000000000005 | aaaaaaaa-aaaa-aaaa-aaaa-000000000001 | cccccccc-cccc-cccc-cccc-000000000005 |         4
 bbbbbbbb-bbbb-bbbb-bbbb-000000000017 | aaaaaaaa-aaaa-aaaa-aaaa-000000000002 | cccccccc-cccc-cccc-cccc-000000000003 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000006 | aaaaaaaa-aaaa-aaaa-aaaa-000000000002 | cccccccc-cccc-cccc-cccc-000000000006 |         2
 bbbbbbbb-bbbb-bbbb-bbbb-000000000007 | aaaaaaaa-aaaa-aaaa-aaaa-000000000002 | cccccccc-cccc-cccc-cccc-000000000000 |         3
 bbbbbbbb-bbbb-bbbb-bbbb-000000000008 | aaaaaaaa-aaaa-aaaa-aaaa-000000000002 | cccccccc-cccc-cccc-cccc-000000000001 |         4
 bbbbbbbb-bbbb-bbbb-bbbb-000000000011 | aaaaaaaa-aaaa-aaaa-aaaa-000000000003 | cccccccc-cccc-cccc-cccc-000000000004 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000010 | aaaaaaaa-aaaa-aaaa-aaaa-000000000003 | cccccccc-cccc-cccc-cccc-000000000003 |         2
 bbbbbbbb-bbbb-bbbb-bbbb-000000000009 | aaaaaaaa-aaaa-aaaa-aaaa-000000000003 | cccccccc-cccc-cccc-cccc-000000000002 |         3
 bbbbbbbb-bbbb-bbbb-bbbb-000000000012 | aaaaaaaa-aaaa-aaaa-aaaa-000000000004 | cccccccc-cccc-cccc-cccc-000000000005 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000013 | aaaaaaaa-aaaa-aaaa-aaaa-000000000004 | cccccccc-cccc-cccc-cccc-000000000006 |         2
 bbbbbbbb-bbbb-bbbb-bbbb-000000000014 | aaaaaaaa-aaaa-aaaa-aaaa-000000000004 | cccccccc-cccc-cccc-cccc-000000000000 |         3
(18 rows)

We can now obtain only the first C entry associated to a A entry by selecting only entries with rowNumber=1:

select * 
  from (
    select id,id_a,id_c,ROW_NUMBER() 
    over (partition by id_a order by id_a) as rowNumber from B) 
  as aggregate 
  where aggregate.rowNumber=1;

Result:

                  id                  |                 id_a                 |                 id_c                 | rownumber 
-------------------------------------- -------------------------------------- -------------------------------------- -----------
 bbbbbbbb-bbbb-bbbb-bbbb-000000000000 | aaaaaaaa-aaaa-aaaa-aaaa-000000000000 | cccccccc-cccc-cccc-cccc-000000000000 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000004 | aaaaaaaa-aaaa-aaaa-aaaa-000000000001 | cccccccc-cccc-cccc-cccc-000000000004 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000017 | aaaaaaaa-aaaa-aaaa-aaaa-000000000002 | cccccccc-cccc-cccc-cccc-000000000003 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000011 | aaaaaaaa-aaaa-aaaa-aaaa-000000000003 | cccccccc-cccc-cccc-cccc-000000000004 |         1
 bbbbbbbb-bbbb-bbbb-bbbb-000000000012 | aaaaaaaa-aaaa-aaaa-aaaa-000000000004 | cccccccc-cccc-cccc-cccc-000000000005 |         1
(5 rows)

By joining C it is now possible to sort by lastname:

select aggregate.id_a,c.lastname 
  from (
    select id,id_a,id_c,ROW_NUMBER() 
    over (partition by id_a order by id_a) as rowNumber from B) 
  as aggregate       
  join C as c on aggregate.id_c=c.id where aggregate.rowNumber=1 
  order by c.lastname;

Result:

                 id_a                 | lastname 
-------------------------------------- ----------
 aaaaaaaa-aaaa-aaaa-aaaa-000000000004 | C five
 aaaaaaaa-aaaa-aaaa-aaaa-000000000003 | C four
 aaaaaaaa-aaaa-aaaa-aaaa-000000000001 | C four
 aaaaaaaa-aaaa-aaaa-aaaa-000000000002 | C three
 aaaaaaaa-aaaa-aaaa-aaaa-000000000000 | C zero
(5 rows)

(Tested with Postgres 14.3)