Drizzle Team•3w ago

Smart way to prepare data for frontend

As you can imagine, query returns aren't exacly pretty, and they'll have rows for each value that matches. For example, let's say you want to grab posts and comments. On your select, you'd have a row (or in drizzle case, an object for each row) for each post and comment. something like

[{postId:"1",commentId:"1",text:""},{postId:"1",commentId:"2",text:""}]

[{postId:"1",commentId:"1",text:""},{postId:"1",commentId:"2",text:""}]

Is there a way (without scripting on the js side), to map certain keys to a schema you want to have? In this scenario I'd like to have something like

[{postId:"1",comments:[{id:1, text:""},{id:2,text:""}]]

[{postId:"1",comments:[{id:1, text:""},{id:2,text:""}]]

This is just a simple example, let me know if my explanation wasn't clear enough.

8 Replies

francis•3w ago

use the query api with relations, whcih will give you a nested data structure?

francis•3w ago

https://orm.drizzle.team/docs/rqb

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

DavidOP•3w ago

Managed to completely skip that doc, only checked the select one. Yea this should do everything I want much appreciated @francis reckon there's a way to fix

invalid reference to FROM-clause entry for table jobs

invalid reference to FROM-clause entry for table jobs

It's caused by what's inside the where

  const jobsDb = await db.query.jobs_table.findMany({
    where: notExists(
      db
        .select()
        .from(job_user_statuses_table)
        .where(
          and(
            eq(
              job_user_statuses_table.job_id,
              jobs_table.id,
            ),
            eq(job_user_statuses_table.accepted, true),
            eq(job_user_statuses_table.user_id, userId),
          ),
        ),
    ),
    orderBy: sql`RAND()`,
    with: {
      working_days: true,
      location: true,
      location_image_file: true,
      required_languages: true,
      required_skills: true,
    },
  });

  const jobsDb = await db.query.jobs_table.findMany({
    where: notExists(
      db
        .select()
        .from(job_user_statuses_table)
        .where(
          and(
            eq(
              job_user_statuses_table.job_id,
              jobs_table.id,
            ),
            eq(job_user_statuses_table.accepted, true),
            eq(job_user_statuses_table.user_id, userId),
          ),
        ),
    ),
    orderBy: sql`RAND()`,
    with: {
      working_days: true,
      location: true,
      location_image_file: true,
      required_languages: true,
      required_skills: true,
    },
  });

Reckon that it can't infer where jobs_table comes from because it's nested inside another db request? If not is there a way to add a join and use the where logic onthe query?

DavidOP•3w ago

Is this related to the subqueries inside query?

francis•3w ago

no idea I don't believe you can mix the query interface and where conditions on joined tables though

DavidOP•3w ago

so queries wouldn't work for in my use case? that sucks

francis•3w ago

I dunno if they would. A potential option is to select a list of ids, then use that as an inArray input in your query, but it's not ideal

DavidOP•3w ago

yea thats really not ideal, goona make another thread for that hopefully theres an answer

Gaming

Programming

Smart way to prepare data for frontend