Sebastián Comments - Answer Overflow

Sebastián

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

TLDR: Xampp/phpmyAdmin didn't recognize some SQL generated code (Serial column declaration, some JSON inside select). But I already tested all code on PlanetScale and everything works. I guess I'll do the same and develop on a dev branch as well

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

Hi, I'm just gonna work directly on planetscale for the time being. But I'm curious about what mysql tool should I use on Windows for local development

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

well, planetscale works flawlessly, I guess you shouldn't use xampp/phpMyAdmin for local development

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

I'll test the same code on Planetscale later, if it works it means xampp is partially not supported by default

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

got the logger running and theese are the queries generated: using "with" "city" doesn't work

select id, name, code, cast(city as json) from (select country.id, country.name, country.code, if(count(country_city.country_id) = 0, '[]', json_arrayagg(json_array(country_city.id, country_city.name, country_city.country_id))) as city from country left join city country_city on country.id = country_city.country_id group by country.id) country where country.id = ? -- params: [3]

not using "with" works select id, name, code from country where country.id = ? -- params: [3] this works

db.select({
        country,
        city
    }).from(country)
    .leftJoin(city, eq(city.countryId, country.id))
    .where(eq(country.id, 3))

and generates

select country.id, country.name, country.code, city.id, city.name, city.country_id from country left join city on city.country_id = country.id where country.id = ? -- params: [3]

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

So my guess is the database running on xampp is not compatible with some functinality?

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

Also notices drizzle-kit push:mysql failed if I used serialas the type for id on any table

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

Same error from last time:

// http://localhost:3000/api/country

{
  "url": "/api/country",
  "statusCode": 500,
  "statusMessage": "",
  "message": "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json) from (select

country.id, country.name, country.code

, if(cou...' at line 1",
  "stack": "<pre><span class=\"stack internal\">at PromiseConnection.query (C:\\Users\\username\\Documents\\GitHub\\projectname\\node_modules\\.pnpm\\[email protected]\\node_modules\\mysql2\\promise.js:94:22)</span>\n<span class=\"stack internal\">at MySql2PreparedQuery.execute (/C:/Users/username/Documents/GitHub/projectname/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/mysql2/index.mjs:51:37)</span>\n<span class=\"stack internal\">at QueryPromise.execute (/C:/Users/username/Documents/GitHub/projectname/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/session-9628aea0.mjs:1329:31)</span>\n<span class=\"stack internal\">at QueryPromise.then (/C:/Users/username/Documents/GitHub/projectname/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)</span>\n<span class=\"stack internal\">at process.processTicksAndRejections (node:internal/process/task_queues:95:5)</span></pre>"
}

Works without "with":

export default defineEventHandler(async (e) => {
  const result = await db.query.country.findFirst({
      with: {
          
      },
      where: eq(country.id, 3) // Albania populated with 74 cities
  })
  return result
})

// http://localhost:3000/api/country

{
  "id": 3,
  "name": "Albania",
  "code": "AL"
}

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

Hey I'm back, just added 2 simple tables: Country and City, country has many cities. I thought I'd also mention the environment I'm working on: OS: Windows 10 Framework: Nuxt3 Database: phpMyAdmin 5.2.0 on local via Xampp 8.1.12 Mariadb Schema

export const country = mysqlTable("country", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }).notNull(),
  code: varchar("code", { length: 255 }).notNull()
})

export const countryRelations = relations(country, ({many}) => ({
  city: many(city)
}))

export const city = mysqlTable("city", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }).notNull(),
  countryId: int("country_id").references(() => country.id).notNull()
})

export const cityRelations = relations(city, ({one}) => ({
  country: one(country, {
    fields: [city.countryId],
    references: [country.id]
  })
}))


export type Country = InferModel<typeof country>
export type City = InferModel<typeof city>

API country.get.ts

import { eq } from "drizzle-orm"
import { country } from "../schema/schema"

export default defineEventHandler(async (e) => {
    const result = await db.query.country.findFirst({
        with: {
            city: true
        },
        where: eq(country.id, 3) // Country is Albania, with 74 cities on the Database
    })
    return result
})

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

maybe I'm doing something dumb cause it's too late

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

but I'll make it work tomorrow, thanks for your time

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

I just want the JSON response to not just retrieve, gender_id: 3, but gender : { ...properties } as a nested object

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

renamed "myGender" to just "gender"

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

it's actually pretty simple, surprised it's not working

export const costume = mysqlTable("costume", {
  id: int("id").primaryKey().autoincrement(),
  imageSrc: varchar("image_src", {length: 255}),
  leased: boolean("leased").default(false),
  name: varchar("name", {length: 255}).notNull(),
  sizeId: int("size_id").references(() => size.id).notNull(),
  genderId: int("gender_id").references(() => gender.id).notNull(),
  seriesId: int("series_id").references(() => series.id).notNull(),
  studioId: int("studio_id").references(() => studio.id).notNull()
})
export const costumeRelations = relations(costume, ({one}) => ({
  gender: one(gender, {
    fields: [costume.genderId],
    references: [gender.id]
  })
}))

export const gender = mysqlTable("gender", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", {length: 255}).notNull()
})

and then on the API

async function getCostume(query: any) {

    const costumeId = query.id
    const result = await db.query.costume.findMany({
        with: {
            gender: true
        }
        ,
        where: eq(costume.id, costumeId)
    })
    return result
    
}

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

ok thanks, I'll try making it work on a fresh project tomorrrow following examples on docs step by step

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

nope, just what I called the field inside the relationship, but also tested is by giving the name "gender", same error

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

tables are costume and gender, relation one to one for simplicity and testing const costumeId = 1

const result = await db.query.costume.findMany({
        with: {
            myGender: true
        },
        where: eq(costume.id, costumeId)
    })

36 replies

DTDrizzle Team

•Created by Sebastián on 7/29/2023 in #help

Are relational queries supported on mysql?

Thanks, now findFirst and findMany are working, but for some reason when I include a with: { exRelation: true} , I get "You have an error in your SQL syntax;"

36 replies

Gaming

Programming