Can these two queries be optimised into a single one?-CodePudding

Given the tables:

create table entries
(
    id           integer generated always as identity
        constraint entries_pk
            primary key,
    name         text    not null,
    description  text,
    type         integer not null
);

create table tasks
(
    id          integer generated always as identity
        constraint tasks_pk
            primary key,
    channel_id  bigint               not null,
    type        integer              not null,
    is_active   boolean default true not null
);

I currently have two separate queries. First:

SELECT id FROM tasks WHERE is_active = true;

Then, once per result from the last query:

SELECT t.channel_id, e.name, e.description
FROM tasks t
JOIN entries e ON t.type = e.type
WHERE t.id = :task_id
ORDER BY random()
LIMIT 1;

In other words I want a single random entry for each active task.

Can this be accomplished in a single query while retaining the limit per task?

CodePudding user response：

Sure; use DISTINCT ON:

SELECT DISTINCT ON (t.id)
       t.id, t.channel_id, e.name, e.description
FROM tasks t
JOIN entries e USING (type)
ORDER BY t.id, random();