one to many query

In Drizzle you can make a one to many query like this:

const result = await db.query.users.findMany({
    with: {
        posts: true            
    },
});

const result = await db.query.users.findMany({
    with: {
        posts: true            
    },
});

with that query I get the user and its posts there are some questions that I have 1. Can I use pure SQL in that query that uses the ORM-like syntax? 2. how can I write a query with the SQL-like syntax that allows me to select for each table separate fields

12 Replies

Angelelz•14mo ago

I don't understand your first question. The syntax in that query is called relational queries. And it produces pure sql. Your second question, we've gone over it before, in your select statement you can select whatever you want from any table that's included in the join If you have a sql query that you'd like to translate to drizzle syntax I can help, What is the sql query that you're looking for?

ippoOP•14mo ago

@Angelelz thanks for response! I just studied what sql is doing, and believe it or not, it just creates the same SQL that you would write in pure SQL to get the same result tired

this cases it uses json functions to aggregate the children in a single field so a the moment I am able to get the most efficient query with the relational query thanks for now, when there is query that I need to translate drizzle, I will let you know 🙂 @Angelelz how can I tell drizzle, to put a single value in posts? for example:

const result = await db.query.users.findMany({
    with: {
        posts: posts.content  // <----only the first post should be fetched and only a string should be returned             
    },
});

const result = await db.query.users.findMany({
    with: {
        posts: posts.content  // <----only the first post should be fetched and only a string should be returned             
    },
});

I know that I can use where to select a specific post and limit to pick only one but how can I format the picked row?

Angelelz•14mo ago

According to the docs: https://orm.drizzle.team/docs/rqb#nested-partial-fields-select

Drizzle Queries - DrizzleORM

Drizzle ORM | %s

Angelelz•14mo ago

const result = await db.query.users.findMany({
    with: {
        posts: {
            columns: { content: true },
            limit: 1
        }
    },
});

const result = await db.query.users.findMany({
    with: {
        posts: {
            columns: { content: true },
            limit: 1
        }
    },
});

It's worth taking a look at the docs, the team put a lot of effort into it

ippoOP•14mo ago

@Angelelz this will give you an array of a single object what I want is single string with other words:

typeof result.users[0].posts === "string"  // true

typeof result.users[0].posts === "string"  // true

makes sense 🙂 ?

Angelelz•14mo ago

That functionality is not available in the relational query API

ippoOP•14mo ago

hmm... you mean I can not use pure SQL that can reduce it to a single value? @Angelelz I was able to do that with the sql-like syntax

NoBody•14mo ago

hi angelez, i tried to use this kind of query, with limit 1, while in the db, my selected entity have thousands of relation record then the query starts to be very slow

Angelelz•14mo ago

Can you show it here so others can see an example

ippoOP•14mo ago

@Angelelz sure this query reduced the phonenumbers into a single value. So with other words users[0].number is just a string:

return db
    .select({
      id: contacts.id,
      firstName: contacts.firstName,
      lastName: contacts.lastName,
      number: phoneNumbers.number,
    })
    .from(contacts)
    .leftJoin(phoneNumbers, eq(contacts.id, phoneNumbers.contactId));

return db
    .select({
      id: contacts.id,
      firstName: contacts.firstName,
      lastName: contacts.lastName,
      number: phoneNumbers.number,
    })
    .from(contacts)
    .leftJoin(phoneNumbers, eq(contacts.id, phoneNumbers.contactId));

is there a way to do that with .findMany() ?

Angelelz•14mo ago

Isn't that the same query I sent you 2 days ago over DM? If you want fine-grain control of what you want in your query, you need to use the Crud API

ippoOP•14mo ago

@Angelelz : no, I had this query before but I do not understand your thing with the "Crud API" can you clearify that AND can you tell me if this is possible to it with findMany?

Gaming

Programming

one to many query