[FIXED] org.postgresql.util.PSQLException: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

Issue

I have 2 entities with unidirectional association.

@Entity
@Table(name = "albums")
public class Album {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String ownerId;
    private String name;
    private Boolean isPublic;

    @OneToMany(orphanRemoval = true)
    @JoinTable(
            name = "album_album_cards",
            joinColumns = @JoinColumn(name = "album_id"),
            inverseJoinColumns = @JoinColumn(name = "album_card_id"))
    private List<AlbumCard> cards;
}

@Entity
@Table(name = "album_cards")
public class AlbumCard {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Integer price;
    private String condition;
    private String design;
    private Integer count;
    private Long cardId;
    @UpdateTimestamp
    private LocalDate updated;

}

And three tables albums, album_cards and album_album_cards (for mapping)

When i map entity to model the exception is throws.

2022-11-14 21:37:57.725 ERROR 18696 --- [nio-9999-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

org.postgresql.util.PSQLException: ERROR: column "cards0_.album_id" must appear in the GROUP BY clause or be used in an aggregate function

    at ru.berserkdeck.albums.impl.mapper.AlbumMapper.albumCardListToAlbumPositionModelList(AlbumMapper.java:57) ~[classes/:na]
    at ru.berserkdeck.albums.impl.mapper.AlbumMapper.toModel(AlbumMapper.java:31) ~[classes/:na]
    at java.base/java.util.Optional.map(Optional.java:260) ~[na:na]
    at ru.berserkdeck.albums.impl.service.AlbumServiceImpl.getAlbum(AlbumServiceImpl.java:50) ~[classes/:na]

Last sql logs is

Hibernate: select album0_.id as id1_1_, album0_.is_public as is_publi2_1_, album0_.name as name3_1_, album0_.owner_id as owner_id4_1_ from albums album0_ where album0_.owner_id=? and album0_.id=?
Hibernate: select cards0_.album_id as album_id8_0_0_, cards0_.id as id1_0_0_, cards0_.id as id1_0_1_, cards0_.card_id as card_id2_0_1_, cards0_.condition as conditio3_0_1_, cards0_.count as count4_0_1_, cards0_.design as design5_0_1_, cards0_.price as price6_0_1_, cards0_.updated as updated7_0_1_ from album_cards cards0_ where cards0_.album_id=?

mapper code:

51    protected List<AlbumPositionModel> albumCardListToAlbumPositionModelList(List<AlbumCard> list) {
52        if (list == null) {
53            return new ArrayList<>();
54        }
55
56        List<AlbumPositionModel> list1 = new ArrayList<>();
57        list.forEach(e -> list1.add(albumCardToAlbumPositionModel(e))); <---- exception throws there. And it throws if i call any method of list (List<AlbumCard>)
58        return list1;

the service method, calling the mapper(i tried with Transactional annotation and without, result the same):

    @Override
    public Optional<AlbumModel> getAlbum(String ownerId, Long albumId) {
        if (ownerId != null) {
            return albumRepo
                    .findByOwnerIdAndId(ownerId, albumId)
                    .map(mapper::toModel);
        } else {
            return albumRepo
                    .findByIdAndIsPublic(albumId, true)
                    .map(mapper::toModel);
        }
    }

Anyone could help me? What I'm dooing wrong?

Solution

Try renaming the count column from

private Integer count;

to

@Column(name = "CARD_COUNT")
private Integer count;

Answered By - Reimeus
Answer Checked By - Robin (JavaFixing Admin)