tomhill Posts - Answer Overflow

tomhill

•Created by tomhill on 5/17/2024 in #help

orderBy in nested findMany

The docs suggest that I can order a nested findMany (https://orm.drizzle.team/docs/rqb#order-by) Like so:

await db.query.posts.findMany({
  orderBy: (posts, { asc }) => [asc(posts.id)],
  with: {
    comments: {
      orderBy: (comments, { desc }) => [desc(comments.id)],
    },
  },
});

await db.query.posts.findMany({
  orderBy: (posts, { asc }) => [asc(posts.id)],
  with: {
    comments: {
      orderBy: (comments, { desc }) => [desc(comments.id)],
    },
  },
});

But when I attempt to do something similar:

  const projects = await db().query.projects.findMany({
    with: {
      projectInfo: {
        orderBy: (projectInfo, { asc }) => [asc(projectInfo.name)]
      },
    },
  });

  const projects = await db().query.projects.findMany({
    with: {
      projectInfo: {
        orderBy: (projectInfo, { asc }) => [asc(projectInfo.name)]
      },
    },
  });

I get the type error : Object literal may only specify known properties, and orderBy does not exist in type How can I orderBy an attribute in a nested query? The projectInfo is a one-to-one relation with projects. I want to order projects by projectInfo.name? Is there a way I can do this using the query syntax?

2 replies

DTDrizzle Team

•Created by tomhill on 5/9/2024 in #help

Table alias is greater than 63 bytes causes truncated identifier notice

I have a query:

const samplingRound = await db.query.samplingRound
    .findFirst({
      where: ops.eq(schema.samplingRound.id, samplingRoundId),
      with: {
        carbonEstimationArea: true,
        stratumSamplingRounds: {
          with: {
            soilSamples: {
              with: {
                soilSampleAnalysis: true,
              },
            },
            stratum: true,
          },
        },
      },
    })

const samplingRound = await db.query.samplingRound
    .findFirst({
      where: ops.eq(schema.samplingRound.id, samplingRoundId),
      with: {
        carbonEstimationArea: true,
        stratumSamplingRounds: {
          with: {
            soilSamples: {
              with: {
                soilSampleAnalysis: true,
              },
            },
            stratum: true,
          },
        },
      },
    })

which generates some SQL that alias the table name soil_sample_analysis to samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis which is 66 bytes:

from "soil_sample_analysis" "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis"
where "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis"."soil_sample_id" = "samplingRound_stratumSamplingRounds_soilSamples"."id"

from "soil_sample_analysis" "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis"
where "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis"."soil_sample_id" = "samplingRound_stratumSamplingRounds_soilSamples"."id"

Because 66 > 63, I get this notice:

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42622',
  message: 'identifier "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis" will be truncated to "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnaly"',
  file: 'scansup.c',
  line: '99',
  routine: 'truncate_identifier'
}

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42622',
  message: 'identifier "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnalysis" will be truncated to "samplingRound_stratumSamplingRounds_soilSamples_soilSampleAnaly"',
  file: 'scansup.c',
  line: '99',
  routine: 'truncate_identifier'
}

What are my options here for preventing this? I don't want to rename the table, as the individual tables themselves are a reasonable length. I also ideally don't want to just suppress the notice using the driver. I see this as an issue with drizzle as drizzle is generating this alias. Thanks for your help!

2 replies

Gaming

Programming