zahdonk Posts - Answer Overflow

zahdonk

•Created by zahdonk on 2/24/2024 in #help

joining tables from different SQL Schemas MARIA Db

Hi, we have a multi-schema Maria Db and in the old codeignitor query language it was possible to join tables even though they were not part of the same sql schema. Does this work in drizzle as well? I tried to setup the multischema part like described in the docs, but i am struggling with the database connection.

const firstSchema = mysqlSchema("firstSchema")

const firstSchemaTable = firstSchema.table("table_one", {...})


const secondSchema = mysqlSchema("secondSchema")

const secondSchemaTable = secondSchema.table("table_one", {...})

const firstSchema = mysqlSchema("firstSchema")

const firstSchemaTable = firstSchema.table("table_one", {...})


const secondSchema = mysqlSchema("secondSchema")

const secondSchemaTable = secondSchema.table("table_one", {...})

The schema names are matching with the schemas/databases in the mariaDb The question now is: What do I need to provide as database name in order to make this work? At the end I want to be able to do something like this:

const myQueryResult = await db
.select()
.from(firstSchemaTable)
.leftJoin(secondSchemaTable, eq(firstSchemaTable.secondTableFk, secondSchemaTable.id))

const myQueryResult = await db
.select()
.from(firstSchemaTable)
.leftJoin(secondSchemaTable, eq(firstSchemaTable.secondTableFk, secondSchemaTable.id))

or in plain SQL

SELECT *
FROM "firstSchema".table_one to
JOIN "secondSchema".table_one tt ON to.secondTableFk = tt.id

SELECT *
FROM "firstSchema".table_one to
JOIN "secondSchema".table_one tt ON to.secondTableFk = tt.id

is that possible with drizzle?

1 replies

DTDrizzle Team

•Created by zahdonk on 9/18/2023 in #help

How to join tables from different dbs in same db cluster?

Hi is there the possibility to join tables from different schemas? We have a mariadb cluster with different schemas and would like to join tables from different schemas. Is that already possible with drizzle?

4 replies

Gaming

Programming