How go do joins based on non-primary key INT columns?

[byronthe123]

Could you please describe how to do joins between two tables using non-integer and non-primary key columns. For example, when using SQL Server, I could write the following query: SELECT * FROM TableA JOIN TAble B ON TableA.guid = TableB.tableAguid;. How could I do this with RDB? Thank you for your help, I think this is library is a great tool and I'm still learning how to use it correctly.

[lroal]

Hello !
Is it like a one-to-many relation ? So that each row in TableA has many "child" rows in TableB ?
And tableA.guid is the primary key of TableA ?
Orange ORM requires that at either the left or right side of the join must be the primary key ? However, it is allowed define a logical primary in the schema that is not the actual physical primary key - but it needs to be unique.

[lroal]

Like this with guid, but you can use varchar as well.:

import orange from 'orange-orm';

const map = orange.map(x => {
    return {
        a: x.table('tableA').map(v => {
            return {
                guid: v.column('guid').uuid().primary(),
            }
        }),

        b: x.table('tableB').map(v => {
            return {
                id: v.column('id').uuid().primary(),
                tableAguid: v.column('tableAguid').uuid(),
            }
        }),

    }
}).map(x => ({
    a: x.a.map(v => ({
        bchildren: v.hasMany(x.b).by('tableAguid')
    }))
}));

const db = map.sqlite('db');

const rows = await db.a.getAll({
    bchildren: true
}); //[{guid: 1, bChildren: [{id: 99, tableAGuid: 1}, {id: 100, tableAGuid: 1}]}]

[byronthe123]

So I tried this, but the only options I'm getting are INT/number columns in TableB:

If I was able to reference guid (which is a uuid), then it would work.

[lroal]

What is the name of the column in the transferCargo table that relates to rack table ?
If you only get number columns suggestions, it means that rack has number as primary column - not uuid.
Can you show me entire schema ?

Also, you are writing "I need table able to reference this field in the transferCargo schema." That confuses me. That means you should have a rack.transferCargoId column that needs to reference the primary column of transferCargo. And use a "references" instead of "hasOne".

(Also, remember that you don't need to specify the name of primary column in the target table. You only specify the non-primary column in the transferCargo table).

[lroal]

Also make sure you are using the latest release of orange-orm. The package rdb is deprecated.