Drizzle Team•16mo 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•16mo ago

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

ScaiOP•16mo 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•16mo ago

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

ScaiOP•16mo ago

Like sql Select & Joins, right?

Angelelz•16mo ago

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

ScaiOP•16mo 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•16mo ago

No, you have to use the Crud API

ScaiOP•16mo ago

What does that mean Crud API in drizzle docs?

Angelelz•16mo ago

This

ScaiOP•16mo ago

So manual sql like, I understand

Angelelz•16mo 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

Did you find this page helpful?