Postgresql golang sqlx包查询

Question

我使用golang sqlx包和postgresql作为数据库。我有两张简单的桌子

create table items (
    id varchar unique primary key not null,
    is_active bool not null default false
);

create table images (
    item_id varchar not null,
    link varchar unique not null,
    index int not null,
    foreign key (item_id) references items(id) on delete cascade
);

我需要执行的是在一个查询中获得所有已分配图像的项目，之后不再使用go变量进行操作

我有这样的代码来执行该操作

type Image struct {
    ItemID string `db:"item_id" json:"item_id"`
    Index int `db:"index" json:"index"`
    Link string `db:"link" json:"link"`
}

type Images []Image

func (t Images) Value() (driver.Value, error) {
    b, err := json.Marshal(t); if err != nil {
        return driver.Value(""), err
    }
    return driver.Value(string(b)), nil
}

func (t *Images) Scan (src interface{}) error {
    var source []byte

    if reflect.TypeOf(src) == nil {
        return nil
    }

    switch src.(type) {
        case string:
            source = []byte(src.(string))
        case []byte:
            source = src.([]byte)
        default:
            return errors.New("incompatible type for images")
    }

    return json.Unmarshal(source, t)
}

func GetItems (active bool) (items []Item, err error) {
    conn := postgres.Connection()

    const query = `SELECT *, (
        SELECT json_agg(images) AS images FROM (
            SELECT images.link, images.index FROM images GROUP BY images.link, images.index HAVING bool_or(item_id=items.id) AND count(item_id) > 0
        ) as images
    ) FROM items`

    if err := conn.Select(&items, query, active); err != nil {
        logrus.WithError(err).Errorf("Can not get all items")
    }

    return
}

This works but I can have results with items that has no assigned images for them and this code seems tooo complicated for such simple at first sight task. Wish anyone can help me with that

Answer 1

如果我没理解错的话，你想这么做：

select i.id 
from images img
join items i 
 on img.item_id = i.id
where i.isactive =1 
group by i.id

Answer 2

在@eshirvana的帮助下，我找到了这个解决方案：

SELECT items.*, json_agg(images) as images 
FROM items 
JOIN images 
   ON images.item_id = items.id 
WHERE is_active=$1 
GROUP BY items.id