Gorm How can I use an aggregate function inside SELECT statement ?

Your Question

Hello, Imagine that I have 2 tables Clients and Tickets.

So I would like to get a list of clients with a list of theirs tickets for each of them (putted into TicketsList array).

var clientTickets struct {
    Client    ClientDB `db:"client"` // a struct with some fields
    TicketsList pq.StringArray    `db:"tickets"`
} 
var list []models.clientTickets

db.
Joins("LEFT JOIN client_ticket ON client.uuid = client_ticket.client_uuid").
Where("....").
.Find(&clientTickets)

So here the "Smart Select Fields" will list all the necessary fields to get. But, I would like to use a custom postgres function for a TicketsList field of a struct clientTickets.

Like: ARRAY_AGG (client_ticket.ticket_uuid) FILTER (where client_ticket.ticket_uuid IS NOT NULL) tickets

What is the best (possible) way to achieve it ? For now I see only one way, - use .Raw() and write the whole SQL query manually, but it is a little bit laborious.

There exists any other solution ? For ex. using Clause SubQuery or Preload or by another way ?

Finally I need need to have a query like this: SELECT client.uuid, client.other_field, client.another_field,.... ,tickets FROM clients LEFT JOIN ..........;

The document you expected this should be explained

Advanced query

Expected answer

Advice, what feature of GORM I need to use. (except .Raw() =) )

Comment From: balalamba

Solution was found, - just to use .Preload("tickets")

var clientTickets struct {
    Client    ClientDB `db:"client"` // a struct with some fields
    TicketsList TicketDB   `gorm:"many2many:analysis_sample;joinForeignKey:analysisUUID;joinReferences:sampleUUID"`
} 
var list []models.clientTickets

db. 
.Preload("Tickets")
Where("....").
.Find(&clientTickets)