Retrieve random rows from Postgresql DB

[SeRiice]

Hello guys,

I was asking if this is possible to retrieve random rows from Postgresql DB with this ORM.

This is what I tried to do using raw SQL query fragments but I get this error : Trying to order by not existing property Product.sql

getSingle: publicProcedure
    .input(getSingleProductSchema)
    .query(async ({ ctx: { entities, lang, raw }, input: { slug } }) => {
      const product = await entities.product.findOneOrFail(
        { slug },
        { populate: ["images", "materials", "categories"] },
      )
      const similarProducts = await entities.product.find(
        {
          id: { $ne: product.id },
          categories: product.categories.map((category) => category.id),
          stock: { $gt: 0 },
        },
        {
          limit: 6,
          orderBy: raw("RANDOM()"),
        },
      )

      return {
        result: {
          ...formatProduct(product, lang, "product"),
          similarProducts: similarProducts.map(
            (similarProduct) => similarProduct.name[lang],
          ),
        },
      }
    })

For your knowledge, I am using tRPC for my backend development.

[B4nan]

orderBy: raw("RANDOM()"),

This needs to be an object, keys map to property names and values to the order (asc/desc). Try orderBy: { [raw("RANDOM()")]: 'asc' }.

[SeRiice]

Hi, I tried your suggestion and it worked!
Because I don't need to specify the order value, I did this instead : orderBy: { [raw("RANDOM()")]: "" }