Drizzle Team•2y ago

order by array_position does not order the records in postgres

Using array_positions either throws error or ignores the order If i try to do it this way i get an function array_position(record, character varying) does not exist error

const articleData = await db
    .select()
    .from(articles)
    .where(inArray(articles.id, idsToInclude))
      .orderBy(sql`array_position(${idsToInclude}, ${articles.id})`);

const articleData = await db
    .select()
    .from(articles)
    .where(inArray(articles.id, idsToInclude))
      .orderBy(sql`array_position(${idsToInclude}, ${articles.id})`);

If i join the array elements then I don't get an error but the order does not change

const ids = "'f02d422b-c8c7-49df-9240-1dcb5a9f4342','7cf70ee0-2e4c-4360-aa5d-8230973b7ec3','367c861c-3f46-4d1a-b1ac-3a25c13121bd','348ad8ab-e7e1-4bf0-b762-c7df0d9e3a75'";

const articleData = await db
    .select()
    .from(articles)
    .where(inArray(articles.id, idsToInclude))
    .orderBy(sql`array_position(ARRAY[${a}], ${articles.id})`);

const ids = "'f02d422b-c8c7-49df-9240-1dcb5a9f4342','7cf70ee0-2e4c-4360-aa5d-8230973b7ec3','367c861c-3f46-4d1a-b1ac-3a25c13121bd','348ad8ab-e7e1-4bf0-b762-c7df0d9e3a75'";

const articleData = await db
    .select()
    .from(articles)
    .where(inArray(articles.id, idsToInclude))
    .orderBy(sql`array_position(ARRAY[${a}], ${articles.id})`);

However, If I hardcode the ids I get the correct order

const articleData = await db
    .select()
    .from(articles)
    .where(inArray(articles.id, idsToInclude))
      .orderBy(sql`array_position(ARRAY[
      'f02d422b-c8c7-49df-9240-1dcb5a9f4342',
      '7cf70ee0-2e4c-4360-aa5d-8230973b7ec3',
      '367c861c-3f46-4d1a-b1ac-3a25c13121bd',
      '348ad8ab-e7e1-4bf0-b762-c7df0d9e3a75'
    ], ${articles.id})`);

const articleData = await db
    .select()
    .from(articles)
    .where(inArray(articles.id, idsToInclude))
      .orderBy(sql`array_position(ARRAY[
      'f02d422b-c8c7-49df-9240-1dcb5a9f4342',
      '7cf70ee0-2e4c-4360-aa5d-8230973b7ec3',
      '367c861c-3f46-4d1a-b1ac-3a25c13121bd',
      '348ad8ab-e7e1-4bf0-b762-c7df0d9e3a75'
    ], ${articles.id})`);

1 Reply

AmurOP•2y ago

I had to use sql.raw() to get it working

Gaming

Programming

order by array_position does not order the records in postgres