❓ Use array_agg

[sxwebdev]

Hey. I have a problem that I described earlier at this link.

I updated my code a bit and it turns out the following.

package sqlstore

import (
    "context"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/georgysavva/scany/pgxscan"
    "github.com/jackc/pgx/v4"
)

// Service ...
type Service struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
}

// Price ...
type Price struct {
    ID        int        `db:"id"`
    Value     int        `db:"value"`
    Category  string     `db:"category"`
    Available bool       `db:"available"`
    Services  []Service  `db:"services"`
    CreatedAt time.Time  `db:"created_at"`
    DeletedAt *time.Time `db:"deleted_at"`
}

// Event ...
type Event struct {
    ID          int        `db:"id"`
    Name        string     `db:"name"`
    Description string     `db:"description"`
    Address     string     `db:"address"`
    StartDate   time.Time  `db:"start_date"`
    Duration    int        `db:"duration"`
    Prices      []Price    `db:"prices"`
    CreatedAt   time.Time  `db:"created_at"`
    DeletedAt   *time.Time `db:"deleted_at"`
}

// GetEvents ...
func GetEvents(conn *pgx.Conn) ([]Event, error) {
    var items []Event

    err := pgxscan.Select(
        context.Background(),
        conn,
        &items,
        `
            SELECT 
                e.*,
                array_agg(array[pr.*]) prices
            FROM events e LEFT JOIN
                (select
                    p2.*
                from prices p2
                group by p2.id) pr
                ON e.id = pr.event_id
            WHERE e.deleted_at IS NULL GROUP BY e.id ORDER BY e.id DESC
        `,
    )
    if err != nil {
        return nil, err
    }

    return items, nil
}

func main() {

    conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    defer conn.Close(context.Background())

    events, err := GetEvents(conn)
    if err != nil {
        log.Fatalln(err)
    }
    fmt.Printf("%+v\n", events)

}

The question is as follows. How to use array_agg? I am unable to decode the array []Price

scany: scan row into struct fields: can't scan into dest[20]: unknown oid 2287 cannot be scanned into *[]Price

If possible, show with this example how to decode an array from postgres

Thanks

[georgysavva]

Scany can handle any type that pgx can. So it's possible. As mentioned in the https://github.com/jackc/pgx/issues/809 you should define a pgx custom type or use an existing one.

After you decide what custom type you need, here are the docs on how to use a custom pgx type with scany: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.5/pgxscan#hdr-Note_about_pgx_custom_types

I hope it helps!

[sxwebdev]

Hey. Writing our decoder turned out to be not an easy decision. As a result, we corrected our sql request and everything works.

Earlier array_agg(array[pr.*]) prices

Now COALESCE(json_agg(pr.*) FILTER (WHERE pr.id IS NOT NULL), '[]') prices

Are there any disadvantages to this approach? Thanks

[georgysavva]

Hi. Sorry for the long response. From the scany perspective, it's totally fine. I don't see any problems.

[sxwebdev]

OK. Thanks a lot

[pragyanaryal]

I ran into same problem, i want to implement the decoder for my type, but as my type is unknown and is built on runtime using joins, how would I implement decoder ? If you can give be a direction, it would be great .

[georgysavva]

@pragyanaryal Hi and thanks for asking. I need more information about your case, but at the first glance, it seems unrelated to this thread. Could you please create a new issue and provide more details such as: struct definition, SQL query and how you call the scany library. Thanks!