Drizzle Team•14mo ago

Query only one item from Many-To-Many

I would like only to take one stat not all the arrays. Is there a method to replace limit with something which returns only one item.

const res = await ctx.db.query.servers.findMany({
  with: {
    game: true,
    stat: {
      limit: 1,
      orderBy: (table, { desc }) => [desc(table.statId)],
      with: {
        stat: true,
      },
    },
  },
});

const res = await ctx.db.query.servers.findMany({
  with: {
    game: true,
    stat: {
      limit: 1,
      orderBy: (table, { desc }) => [desc(table.statId)],
      with: {
        stat: true,
      },
    },
  },
});

11 Replies

Angelelz•14mo ago

You are setting the limmit and orderBy in the intermediate table Try setting that on the other stat

ScaiOP•14mo ago

How I should do that?

    const res = await ctx.db.query.servers.findMany({
      with: {
        game: true,
        stat: {
          with: {
            stat: {
              limit: 1, <--- error not having this properties
              orderBy: (table, { desc }) => [desc(table.statId)],
            },
          },
        },
      },
    });

    const res = await ctx.db.query.servers.findMany({
      with: {
        game: true,
        stat: {
          with: {
            stat: {
              limit: 1, <--- error not having this properties
              orderBy: (table, { desc }) => [desc(table.statId)],
            },
          },
        },
      },
    });

Doesn't work like I should only get the first oldest entry of stat for the that specific server

Angelelz•14mo ago

I'm afraid you'll have to fallback to the crud API

ScaiOP•14mo ago

Like sql Select & Joins, right?

Angelelz•14mo ago

Your initial query was right, I was just testing this My apologies What was the problem you had?

ScaiOP•14mo ago

This is the response:

{
  id: 1,
  ip: '127.0.0.1',
  port: 25565,
  createdAt: 2023-10-28T20:58:36.000Z,
  updatedAt: null,
  game: {
    id: 1,
    code: 'cs2',
    name: 'Counter-Strike 2',
    image: null,
    createdAt: 2023-10-28T20:58:13.000Z,
    updatedAt: null
  },
  stat: [ { stat: [Object] } ]
}

{
  id: 1,
  ip: '127.0.0.1',
  port: 25565,
  createdAt: 2023-10-28T20:58:36.000Z,
  updatedAt: null,
  game: {
    id: 1,
    code: 'cs2',
    name: 'Counter-Strike 2',
    image: null,
    createdAt: 2023-10-28T20:58:13.000Z,
    updatedAt: null
  },
  stat: [ { stat: [Object] } ]
}

I would like to have

{
  id: 1,
  ip: '127.0.0.1',
  port: 25565,
  createdAt: 2023-10-28T20:58:36.000Z,
  updatedAt: null,
  game: {
    id: 1,
    code: 'cs2',
    name: 'Counter-Strike 2',
    image: null,
    createdAt: 2023-10-28T20:58:13.000Z,
    updatedAt: null
  },
  stat: [Object],
}

{
  id: 1,
  ip: '127.0.0.1',
  port: 25565,
  createdAt: 2023-10-28T20:58:36.000Z,
  updatedAt: null,
  game: {
    id: 1,
    code: 'cs2',
    name: 'Counter-Strike 2',
    image: null,
    createdAt: 2023-10-28T20:58:13.000Z,
    updatedAt: null
  },
  stat: [Object],
}

Angelelz•14mo ago

No, you have to use the Crud API

ScaiOP•14mo ago

What does that mean Crud API in drizzle docs?

Angelelz•14mo ago

This

ScaiOP•14mo ago

So manual sql like, I understand

Angelelz•14mo ago

You'd need to do the aggregation yourself Which basically what the RQB is doing for you

Gaming

Programming

Query only one item from Many-To-Many