DT
Drizzle Team•16mo ago
ippo

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
Angelelz•16mo 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?
ippo
ippoOP•16mo 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
Angelelz•16mo ago
Angelelz
Angelelz•16mo 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
ippo
ippoOP•16mo 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
Angelelz•16mo ago
That functionality is not available in the relational query API
ippo
ippoOP•16mo 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
NoBody•16mo 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
Angelelz•16mo ago
Can you show it here so others can see an example
ippo
ippoOP•16mo 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
Angelelz•16mo 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
ippo
ippoOP•16mo 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?

Did you find this page helpful?