Support for recursive scanning

[Technerder]

Hi, I recently stumbled across this library and was wondering if something like the following is possible.

I have a struct that looks like this

type Comment struct {
    ID       string `db:"id"`
    Text     string `db:"text"`
    ParentID string `db:"parent_id"`
    PostID   string `db:"post_id"`
    Replies  []Comment
}

With a recursive function like so for retrieving data

CREATE OR REPLACE FUNCTION get_comments(parent_comment_id varchar) returns SETOF public.comments AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE x AS (
        -- anchor:
        SELECT * FROM comments WHERE post_id = parent_comment_id UNION ALL
        -- recursive:
        SELECT t.* FROM x INNER JOIN comments AS t ON t.parent_id = x.id
    )
    SELECT * FROM x;
END
$$ LANGUAGE plpgsql;

Can scany retrieve the results of something like SELECT * FROM get_comments('id'); into a comments []Comment or would I need to further process the results myself after retrieving them?

[georgysavva]

Hello! Support for recursive types already in development, see this issue https://github.com/georgysavva/scany/issues/57 and this PR https://github.com/georgysavva/scany/pull/60.

But even when it's done, scany won't be able to handle something like Replies []Comment because it doesn't do any ORM-like manipulations on rows. It knows nothing about relations between objects and simply scans everything into a struct in a flat manner. So it wouldn't be possible with scany even with a simpler setup (without recursion) like that:

type Post struct {
  Replies []Comment
}

[HananoshikaYomaru]

given my query to be

SELECT semesters.id as "semesters.id", semesters.year as "semesters.year", semesters.season as "semesters.season", semesters.created_at as "semesters.created_at", semesters.updated_at as "semesters.updated_at", courses.id as "courses.id", courses.code as "courses.code", courses.semester_id as "courses.semester_id", courses.created_at as "courses.created_at", courses.updated_at as "courses.updated_at" FROM semesters LEFT JOIN courses ON semesters.id = courses.semester_id

it return the following result

Will it be possible to fill it into results using scany?

results := []struct {
        Courses []*struct {
            Id          *string    `json:"id" db:"id"`
            Code        *string    `json:"code" db:"code"`
            Semester_id *string    `json:"semester_id" db:"semester_id"`
            Created_at  *time.Time `json:"created_at" db:created_at"`
            Updated_at  *time.Time `json:"updated_at" db:updated_at"`
        } `db:"courses"`
        Semester *struct {
            Id         string    `json:"id" db:"id"`
            Year       int32     `json:"year" db:"year"`
            Season     string    `json:"season" db:"season"`
            Created_at time.Time `json:"created_at" db:"created_at"`
            Updated_at time.Time `json:"updated_at" db:"updated_at"`
        } `db:"semesters"`
    }{}
    if err := pgxscan.ScanAll(&results, rows); err != nil {
        fmt.Errorf("some error : %s", err)
    }

Somehow if I retrieve something about course in the query, the results will be empty [].

[georgysavva]

Hello. No, it's not possible to use scany like you showed above.

Here is how scany can help in your case though:

type Course struct {
    Id          *string    `json:"id" db:"id"`
    Code        *string    `json:"code" db:"code"`
    Semester_id *string    `json:"semester_id" db:"semester_id"`
    Created_at  *time.Time `json:"created_at" db:created_at"`
    Updated_at  *time.Time `json:"updated_at" db:updated_at"`
}

type Semester struct {
    Id         string    `json:"id" db:"id"`
    Year       int32     `json:"year" db:"year"`
    Season     string    `json:"season" db:"season"`
    Created_at time.Time `json:"created_at" db:"created_at"`
    Updated_at time.Time `json:"updated_at" db:"updated_at"`
}

type Result struct {
    Course *Course `db:"courses"`
    Semester *Semester `db:"semesters"`
}

var results []Result
if err := pgxscan.ScanAll(&results, rows); err != nil {
    fmt.Errorf("some error : %s", err)
}
// After the results variable is filled with your rows data you can perform all types of manipulation and aggregation on it.