DT
Drizzle Team•16mo ago
binajmen

Is it possible to have nested values when doing a leftJoin?

// returns nested results
export async function getLoans(spaceId: string) { 
  return db.query.loans.findMany({
    where: eq(loans.spaceId, spaceId),
    with: {
      property: true,
      financialInstitution: true,
    },
  });
}

// doesn't return nested results
export async function getLoansLJ(spaceId: string) { 
  return db
    .select()
    .from(loans)
    .leftJoin(
      financialInstitutions,
      eq(financialInstitutions.id, loans.financialInstitutionId)
    )
    .where(eq(loans.spaceId, spaceId));
}
// returns nested results
export async function getLoans(spaceId: string) { 
  return db.query.loans.findMany({
    where: eq(loans.spaceId, spaceId),
    with: {
      property: true,
      financialInstitution: true,
    },
  });
}

// doesn't return nested results
export async function getLoansLJ(spaceId: string) { 
  return db
    .select()
    .from(loans)
    .leftJoin(
      financialInstitutions,
      eq(financialInstitutions.id, loans.financialInstitutionId)
    )
    .where(eq(loans.spaceId, spaceId));
}
If not, what is your approach when you're not using the query builder? Should I map the results and enrich them based on the financialInstitutionId? Or is there something I missed?
17 Replies
binajmen
binajmenOP•16mo ago
Anyone? 😅
ArwanceR
ArwanceR•16mo ago
can you give an example of what 1st approach returns and what you're looking for ?
binajmen
binajmenOP•16mo ago
Sure The first example returns: 
"loans": [
  {
    "id": "loan_3neymptddkbg9",
    "name": "test",
    "type": "fixed",
    "financialInstitutionId": "belfius",
    "amount": 1000,
    "annualInterestRate": 1,
    "periodInYears": 10,
    "paymentPeriodicity": 1,
    "monthlyPayment": 8.76,
    "startDate": "2023-08-07T22:00:00.000Z",
    "propertyId": null,
    "spaceId": "rogline",
    "createdAt": "2023-08-08T11:16:56.000Z",
    "updatedAt": "2023-08-08T11:16:56.000Z",
    "deletedAt": null,
    "property": null,
    "financialInstitution": {
      "id": "belfius",
      "name": "Belfius",
      "bic": "BELFIUS",
      "logoUrl": "...",
      "country": "BE"
    }
  },
  ...
]
"loans": [
  {
    "id": "loan_3neymptddkbg9",
    "name": "test",
    "type": "fixed",
    "financialInstitutionId": "belfius",
    "amount": 1000,
    "annualInterestRate": 1,
    "periodInYears": 10,
    "paymentPeriodicity": 1,
    "monthlyPayment": 8.76,
    "startDate": "2023-08-07T22:00:00.000Z",
    "propertyId": null,
    "spaceId": "rogline",
    "createdAt": "2023-08-08T11:16:56.000Z",
    "updatedAt": "2023-08-08T11:16:56.000Z",
    "deletedAt": null,
    "property": null,
    "financialInstitution": {
      "id": "belfius",
      "name": "Belfius",
      "bic": "BELFIUS",
      "logoUrl": "...",
      "country": "BE"
    }
  },
  ...
]
The second example returns: 
"loans": [
  {
    "loans": {
      "id": "loan_3neymptddkbg9",
      "name": "test",
      "type": "fixed",
      "financialInstitutionId": "belfius",
      "amount": 1000,
      "annualInterestRate": 1,
      "periodInYears": 10,
      "paymentPeriodicity": 1,
      "monthlyPayment": 8.76,
      "startDate": "2023-08-07T22:00:00.000Z",
      "propertyId": null,
      "spaceId": "rogline",
      "createdAt": "2023-08-08T11:16:56.000Z",
      "updatedAt": "2023-08-08T11:16:56.000Z",
      "deletedAt": null
    },
    "financial_institutions": {
      "id": "belfius",
      "name": "Belfius",
      "bic": "BELFIUS",
      "logoUrl": "...",
      "country": "BE"
    }
  },
  ...
]
"loans": [
  {
    "loans": {
      "id": "loan_3neymptddkbg9",
      "name": "test",
      "type": "fixed",
      "financialInstitutionId": "belfius",
      "amount": 1000,
      "annualInterestRate": 1,
      "periodInYears": 10,
      "paymentPeriodicity": 1,
      "monthlyPayment": 8.76,
      "startDate": "2023-08-07T22:00:00.000Z",
      "propertyId": null,
      "spaceId": "rogline",
      "createdAt": "2023-08-08T11:16:56.000Z",
      "updatedAt": "2023-08-08T11:16:56.000Z",
      "deletedAt": null
    },
    "financial_institutions": {
      "id": "belfius",
      "name": "Belfius",
      "bic": "BELFIUS",
      "logoUrl": "...",
      "country": "BE"
    }
  },
  ...
]
In the first case, the financialInsitution is nested in the loan object, as expected. I understand a pure SQL join would produce the same "non-nested" results, although it would be nice to have the same format. If I miss something in the doc, I would be happy to read more about it. If not, I'm interested how people handle this.
ArwanceR
ArwanceR•16mo ago
I see, have you thought about using a subquery?
binajmen
binajmenOP•16mo ago
I did not How would you do that?
ArwanceR
ArwanceR•16mo ago
modifying second example: 
export async function getLoansLJ(spaceId: string) { 
  return db
    .select({
      financial_institutions: sql`(SELECT id,name,bic,logoUrl,country from financialInstitutions where financialInstitutions.id = 
loans.financialInstitutionId))`
})
    .from(loans)
    .where(eq(loans.spaceId, spaceId));
}
export async function getLoansLJ(spaceId: string) { 
  return db
    .select({
      financial_institutions: sql`(SELECT id,name,bic,logoUrl,country from financialInstitutions where financialInstitutions.id = 
loans.financialInstitutionId))`
})
    .from(loans)
    .where(eq(loans.spaceId, spaceId));
}
this might need some modifications, such as selecting more fields in select({})
binajmen
binajmenOP•16mo ago
mmh it seems a bit hacky no? 😅 I could check how they implemented with, but I'm unsure I will be able to decipher everything 🙈
ArwanceR
ArwanceR•16mo ago
well, I there was another solution, I would too use it : ) this is one solution, you might find other
binajmen
binajmenOP•16mo ago
Yeah thank you for your input, I'll check the source and in parallel hope someone will jump in as well 😉
ArwanceR
ArwanceR•16mo ago
you can turn logging on and check the generated query with this 'working' example what sql it generates
binajmen
binajmenOP•16mo ago
Ho, I didn't think about it
ArwanceR
ArwanceR•16mo ago
know where to put it ?
binajmen
binajmenOP•16mo ago
I suppose in the main instance unless we can activate logging per request also?
ArwanceR
ArwanceR•16mo ago
I don't think you can turn on logging per request but, to turn it on for all queries: 
export const db = drizzle(pool, {
  logger: true
})
export const db = drizzle(pool, {
  logger: true
})
but, that being said, I don't think sql will make you find other solution, since it's raw if there's no other API you will still have to use some workaround
binajmen
binajmenOP•16mo ago
https://github.com/drizzle-team/drizzle-orm/blob/5b01a4ff112935ff5433c3437476a5aa1d86ceab/drizzle-orm/src/relations.ts#L416C17-L416C34 They normalize the relations at some point I will stop digging at the moment and use the query builder. I was asking the question as I expect at some point I'll have more complex queries. So in the end, it's either do like them or do like you I believe 🙂 thanks for the brainstorm, always help 🙂
ArwanceR
ArwanceR•16mo ago
happy to help
Ramazan
Ramazan•16mo ago
You can do something like 
export async function getLoansLJ(spaceId: string) { 
  return db
    .select({ …getTableColumns(loans), financialInstitution: getTableColumns(financialInstitutions) })
    .from(loans)
    .leftJoin(
      financialInstitutions,
      eq(financialInstitutions.id, loans.financialInstitutionId)
    )
    .where(eq(loans.spaceId, spaceId));
}
export async function getLoansLJ(spaceId: string) { 
  return db
    .select({ …getTableColumns(loans), financialInstitution: getTableColumns(financialInstitutions) })
    .from(loans)
    .leftJoin(
      financialInstitutions,
      eq(financialInstitutions.id, loans.financialInstitutionId)
    )
    .where(eq(loans.spaceId, spaceId));
}
You can find this in the docs towards the end of this section
Joins [SQL] – DrizzleORM
Drizzle ORM | %s
Want results from more Discord servers?
Add your server