Nested slice of struct type

[wiliamvj]

I have a many to many relationship in postgres:

CREATE TABLE posts (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP(3) NOT NULL
);

CREATE TABLE users (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP(3) NOT NULL
);

CREATE TABLE users_posts (
  id VARCHAR(36) NOT NULL PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  post_id VARCHAR(36) NOT NULL,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP(3) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  UNIQUE (user_id, post_id)
);

I need to search for the user and all his posts, but SQL does not generate a struct of type []Posts ending up like this:

type FindManyUsersRow struct {
	ID          string
	Name       string
        Email           string
	CreatedAt   time.Time
	Post    Posts
}

I need it to look like this:

type FindManyUsersRow struct {
	ID                 string
	Name          string
        Email           string
	CreatedAt   time.Time
	Post            []Posts
}

my query:

-- name: FindManyUsers :many
SELECT 
u.id, 
u.name, 
p.email, 
u.created_at,
sqlc.embed(c)
FROM users u 
JOIN users_posts up ON up.user_id = u.id
JOIN posts p ON p.id = up.post_id
WHERE 
(up.post_id  = ANY(@categories::TEXT[]) OR @categories::TEXT[] IS NULL)
AND (
  u.name ILIKE '%' || COALESCE(sqlc.narg('search'), @search) || '%' 
OR 
  u.email ILIKE '%' || COALESCE(sqlc.narg('search'), @search)|| '%'
)
ORDER BY p.created_at DESC;

Is it possible to do this with sqlc?

[bessiambre]

This is the composite type scenario. I'm also yearning for this functionality.

In postgres the query would look like.

-- name: FindManyUsersAndTheirPosts :many
SELECT 
u.id, 
u.name, 
u.email, 
u.created_at,
(
    SELECT array_agg(posts) 
    FROM 
    users_posts up
    JOIN posts on posts.id=up.post_id
    WHERE
    up.user_id = u.id
)
FROM users u 
WHERE 
(
  u.name ILIKE '%' || COALESCE(sqlc.narg('search'), @search) || '%' 
OR 
  u.email ILIKE '%' || COALESCE(sqlc.narg('search'), @search) || '%'
);

It's how you would do graphql style queries in postgresql instead of something like

users {
    id
    name
    posts {
      title
    }
  }
}

Unfortunately I don't think it is supported in sqlc.

One workaround is to go through json using to_json but you lose type safety.

SELECT 
u.id, 
u.name, 
u.email, 
u.created_at,
(
    SELECT json_agg(to_json(posts)) 
    FROM 
    users_posts up
    JOIN posts on posts.id=up.post_id
    WHERE
    up.user_id = u.id
)
FROM users u 
WHERE 
(
  u.name ILIKE '%' || 'username' || '%' 
OR 
  u.email ILIKE '%' || '' || '%'
);

[wiliamvj]

I also tried this way, but we lost the security of the alternative types so far I was doing the search without using SQLC.

[pg-goose]

I also have interest on this feature but the issue seems to be stale, no consideration as feature request?

[kyleconroy]

As mentioned in the first comment, this requires support for composite types, which currently isn't being worked on.

I'm open to someone doing the work, but I think it would be a heavy lift with our current architecture.