Support for CAST / CONVERT function ?

[ThomasAribart]

I couldn't find anything in the docs to use the SQL CAST or CONVERT function: https://www.geeksforgeeks.org/sql-query-to-convert-varchar-to-int/

It would be nice to be able to cast columns values and have them impact the inferred type: select(['cast(column as int)'])

[koskimas]

You can call any function or write any expression using the sql tag

select(sql<number>`cast(column as int)`.as('column'))

It's not a good idea to add this helper to the core as Kysely doesn't know the data type mapping from SQL data type to typescript type. That's up to the driver underlying your dialect. For example cast(column as biginteger) can produce number, bigint or string depending on your dialect and settings.

[edgarsilva]

@koskimas @igalklebanov how would this work on a innerJoin? I have this problem where I need to join a table but the colulmns have diff types and I keep getting this ->

For This query ->

const purchaseOrders = await db
    .selectFrom("integration.purchase_order as po")
    .innerJoin("integration.order_data as od",
      sql<string>`od.po_ext_id::text`,
      sql<string>`po.id::text`)
    .select([
      "po.id",
      "od.id",
      "od.po_ext_id",
    ])
    .limit(5)
    .execute();

Which forces me to do this to bypass it ->

const purchaseOrders = await db
    .selectFrom("integration.purchase_order as po")
    .innerJoin("integration.order_data as od",
      sql<string>`od.po_ext_id::text` as unknown as "od.po_ext_id",
      sql<string>`po.id::text` as unknown as "od.po_ext_id")
    .select([
      "po.id",
      "od.id",
      "od.po_ext_id",
    ])
    .limit(5)
    .execute();

is This the correct approack?

Actually I'll just open a new issue.