Question 300660 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – POSTGRES ORDER BY random() with ROW_NUMBER not working as expected

let data = await Category.findAll({
  where: { list_category_id: body.category_id },
  attributes: {
    include: [
      [
        sequelize.literal(`COALESCE (
          CASE WHEN youtube_id_id IS NOT NULL THEN ROW_NUMBER() OVER (ORDER BY random(), youtube_id_id) END,
          CASE WHEN instagram_id_id IS NOT NULL THEN ROW_NUMBER() OVER (ORDER BY random(), instagram_id_id) END )`),
        "row_num",
      ],
    ],
  },
  order: [sequelize.literal("row_num")],
});

In a row only one would have a value either youtube_id_id or instagram_id_id, I came up with this so that I can get rows with values for both youtube_id_id and instagram_id_id column every time, but this only works without the random() in ORDER BY. I want to get random results each time I run this and at the same time ensure rows with both values are present. How can I make this work?

EDIT:

Category Table:

export const Category = sequelize.define(
  "category",
  {
    id: {
      type: DataTypes.UUID,
      defaultValue: sequelize.literal("gen_random_uuid()"),
      primaryKey: true,
    },
    score: {
      type: DataTypes.FLOAT,
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("NOW()"),
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("NOW()"),
    },
  },
  {
    underscored: true,
    tableName: "Category",
  }
);

youtube_id_id and instagram_id_id, are foreign keys.

Sample Data:

id youtube_id_id instagram_id_id
uuid uuid
uuid uuid
uuid uuid
uuid uuid

Expected Output is a bit tricky to describe, I want data for each foreign key, say I limit the data to only 2, so I should always get rows that has values for both keys.

Basically what I think I want it, that when ROW_NUMBER() numbering starts from 1 instead of the first row it assigns it a random row and keep on going. This should get me results at random, is there a way to achieve this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I fixed it, just had to partition by column and group by random.

    COALESCE (CASE WHEN youtube_id_id IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY youtube_id_id IS NOT NULL ORDER BY random()) END,
          CASE WHEN instagram_id_id IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY youtube_id_id IS NOT NULL ORDER BY random()) END)

  2. 0

    I want to get random results each time I run this and at the same time ensure rows with both values are present.

    You can make two queries and union them.

    select *
from your_table
where youtube_id is null
  and instagram_id is not null
order by random()
limit 1

union

select *
from your_table
where youtube_id is not null 
  and instagram_id is null
order by random()
limit 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search