Query annotations for queries with joins that return multiple rows.

[Ianmuhia]

Version

1.12.0

What happened?

When I join two tables and try to select everything from only one of them using the generated code return only one item rather than a list.

Relevant log output

No response

Database schema

-- Example queries for sqlc
create table church_events
(
    id           bigserial
        primary key,
    created_at   timestamp with time zone,
    updated_at   timestamp with time zone,
    deleted_at   timestamp with time zone,
    organizer_id bigint                   not null
        constraint fk_church_events_organizer
            references users
            on update cascade on delete set null,
    cover_image  text                     not null,
    title        text                     not null,
    sub_title    text                     not null,
    content      text                     not null,
    scheduled_on timestamp with time zone not null,
    age          integer                  not null
);

create table church_jobs
(
    duty            text   not null,
    church_event_id bigint not null
        constraint fk_church_event
            references church_events
            on update cascade on delete set null,
    id              bigserial
        primary key,
    created_at      timestamp with time zone not null,,
    updated_at      timestamp with time zone,
    deleted_at      timestamp with time zone
);

SQL queries

-- name: GetEvent :one
select *
from church_events
    left join   church_jobs cj on church_events.id = cj.church_event_id
where church_events.deleted_at IS NULL
  AND church_events.id = $1;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/751428fd61df6380a97a365d86e61818671ea15494a0df5cbf0d5b3ff7de48f0

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

You're only getting one item because you're using the :one command. Changing that to :many will return all the rows.

[Ianmuhia]

hello sir the issue is closed but the bug is still there.suppose i want to return an struct with the following structure

` type ChurchEvent struct {
ID int64
CreatedAt sql.NullTime
UpdatedAt sql.NullTime
DeletedAt sql.NullTime
OrganizerID int64
CoverImage string
Title string
SubTitle string
Content string
ScheduledOn time.Time
Age int32
[] ChurchJob struct {
Duty string
ChurchEventID int64
ID int64
CreatedAt sql.NullTime
UpdatedAt sql.NullTime
DeletedAt sql.NullTime
}

}
`
what would you suppose i do..?

[zlymeda]

Change

-- name: GetEvent :one
select *
... 

to

 -- name: GetEvent :many
select *
... 

[Ianmuhia]

adding :many seems to change nothing ..i want to return a struct with an embedded slice of type struct.

[zlymeda]

oh, as far as I know, that is not possible.

I'm usually using s repository that uses sqlc and it reshapes the structs the way I need them.

Your DB will fetch the rows and sqlc maps tgem to the result 1:1. afaik it doesn't do any reshaping. you're not defining custom structs, sqlc generates them.

[zakaria-chahboun]

This issue should not be closed!