Importing data with nested association

[jorbascrumps]

I'm trying to write an import script for normalized address data with the following setup:

const Address = sequelize.define('address', { ... });
const Cities = sequelize.define('cities', { ... });
const Regions = sequelize.define('regions', { ... });

Address.belongsTo(Cities, {
    foreignKey: 'cityId'
});

Cities.belongsTo(Regions, {
    foreignKey: 'regionId'
});

And below is a sample of the data I'm trying to import:

Region import data:

ID	Region
1	England

City import data:

ID	City
1	London

Address import data:

ID	Owner	Address	City	Region
1	Sherlock Holmes	221B Baker Street	London	England

First, I import the region data, followed by the city data with a reference to the region:

// regions.json
[
    {
        "model": "Regions",
        "data": {
            "label": "England"
        }
    }
]

// cities.json
[
    {
        "model": "Cities",
        "data": {
            "label": "London",
            "region": {
                "label": "England"
            }
        }
    }
]

This works great, but is there a way to import the address data while referencing both the city and region data in order to prevent Holmes from living in London ON, CA? I've tried nesting region data inside of city:

// address.json
[
    {
        "model": "Address",
        "data": {
            "street_1": "221B Baker Street",
            "city": {
                "label": "London",
                "region": {
                    "label": "England"
                }
            }
        }
    }
]

I also tried to be clever and use Sequelize.literal which resulted in this:

[
    {
        "model": "Address",
        "data": {
            "street_1": "221B Baker Street",
            "cityId": {
                "val": "SELECT c.id FROM cities c LEFT JOIN regions r ON c.regionId = r.id WHERE c.label = \"London\" AND r.label = \"England\""
            }
        }
    }
]

Note: I've also posted this question on Stack Overflow.

[domasx2]

Haven't encountered this before. I think the only decent solution right now is to specify IDs for the cities and use those

[matmar10]

Already answered, please reopen if you still need help @jorbascrumps

[jorbascrumps]

Apologies, @domasx2, I hadn't seen your response until now! Thank you for reminding me about this issue, @matmar10.

I've closed the SO question and linked to the solution here.