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 #57 and this PR #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.