How to pass an attribute (different to idAttribute) with 'withRelated'

[ghost]

Hi,
I'm new with bookshelf and I came across a problem with withRelated.
I have 3 models but one of them have a composite key(City) (I know bookshelf doesn't support it):

var Store = bookshelf.Model.extend({
    tableName: 'store',
    idAttribute: 'id',
    addresses: function(){
        return this.hasMany(Address,'store_id');
    }
};

var Address = bookshelf.Model.extend({
    tableName: 'address',
    idAttribute: 'id',
    store: function(){
        return this.belongsTo(Store,'store_id');
    },
    city: function(){
        return this.belongsTo(City,'city_department'); //this is actually a composite fk (city_department,city_province)
    }
}

var City = bookshelf.Model.extend({
    tableName: 'city',
    idAttribute: 'department', //this is a composite pk (department, province)
    addresses: function(){
        return this.hasMany(Address,'city_department');
    }
}

When I fetch all stores and try to get the city from each address I can't seem to pass the city_province attribute from the object.

return new Store()
    .fetchAll({
        withRelated: ['addresses', {'addresses.city': function(qb){
            qb.where('province',?);}}] // '?' have to be city_province attribute from each address model from the result query
    }).then(function(stores){
         res.send(stores.toJSON());
    );

How can I make '?' to be address.city_province?, if I put a fix value at '?' it work, but it retrieve the same city for all addresses.

Then I try to put query at Address Model but this.attributes.city_province always is undefined, the same if I replaced it with this.get('city_province')

var Address = bookshelf.Model.extend({
    tableName: 'address',
    idAttribute: 'id',
    store: function(){
        return this.belongsTo(Store,'store_id');
    },
    city: function(){
        return this.belongsTo(City,'city_department')
                    .query('where','district', '=',this.attributes.city_province);
    }
}

This is my result json without withRelated['addresses.city']

{
    "id": ​1,
    "name": "Store 1",
    "addresses": 
    [
        {
            "id": ​2,
            "address": "Store 1, Av 1",
            "store_id": ​1,
            "city_department": ​15,
            "city_province": ​1
        },
        {
            "id": ​3,
            "address": "Store 1, Av 2",
            "store_id": ​1,
            "city_department": ​15,
            "city_province": ​1
        }
    ]
},
{
    "id": ​2,
    "name": "Store 2",
    "addresses": 
    [
        {
            "id": ​1,
            "address": "Store 2, Av 1",
            "store_id": ​2,
            "city_department": ​15,
            "city_province": ​1
        }
    ]
}

I've read thought the issues but didn't find something that I can use. Hope you can help me.

[ricardograca]

So you want to show only stores from a certain province? Or is it something else? It's not very clear what you're after.

If you use:

return new Store()
    .fetchAll({
        withRelated: ['addresses.city']
    }).then(function(stores) {
        // ...
    })

You should get all the stores with all addresses with all the related cities. No need to specify both ['addresses', 'addresses.city'], since the first is already implicitly fetched when fetching the second. Isn't this returning related cities?

[ghost]

Hi, the problem it's that City model has a composite key. If I try to do it with addreses.city it will only retrieve city_department and I need city_department and city_province.

Try it with query builder but I can't find I way to pass the second parameter

return new Store()
    .fetchAll({
        withRelated: ['addresses', {'addresses.city': function(qb){
            qb.where('province',?);}}] // '?' have to be city_province attribute from each address model from the result query
    }).then(function(stores){
         res.send(stores.toJSON());
    );

I also try to query on the model, but this.attributes.city_province it's always undefined

var Address = bookshelf.Model.extend({
    tableName: 'address',
    idAttribute: 'id',
    store: function(){
        return this.belongsTo(Store,'store_id');
    },
    city: function(){
        return this.belongsTo(City,'city_department')
                    .query('where','district', '=',this.attributes.city_province);
    }
}

[ricardograca]

I don't understand what you mean when you say you need city_province. If that attribute is part of the City model it should be retrieved even if it's used on a composite key.

[ghost]

This are the queries from the debug.

{ method: 'select',
  options: {},
  bindings: [],
  sql: 'select `store`.* from `store`' }
{ method: 'select',
  options: {},
  bindings: [ 1, 2 ],
  sql: 'select `address`.* from `address` where `address`.`store_id` in (?, ?)' }
{ method: 'select',
  options: {},
  bindings: [ 15 ],
  sql: 'select `city`.* from `city` where `city`.`department` in (?)' 

When it fetch address.city always use the primary key defined on the model (``idAttribute = id) so in my example city_department = 15`. But I need to be `city_department = 15 and city_province = ?` where '?' depends on the value from the result.

[ricardograca]

The result of what?

You mentioned queries, models and results, but don't actually post the code that is generating the results. That makes it harder to help you.

Also, I'm totally lost on what your DB schema looks like because you keep mentioning column names and attributes that don't match up. You talk about city_department, but apparently there's no such thing. It's actually just department. However your first JSON result in your first comment actually has city_department 😕 . You also mix the terms "province" and "district", which further adds to the confusion.

So, a little clarification on what your DB looks like would help.

[ghost]

I just notice that, my bad....sorry (problem with the copy&paste, because it's actually a composite key with 3 keys, but I try to minimize it using just 2 here). I'm using MySQL, this is how my schema and model are right now:

var Store = bookshelf.Model.extend({
    tableName: 'store',
    idAttribute: 'id',
    addresses: function(){
        return this.hasMany(Address,'store_id');
    }
};

var Address = bookshelf.Model.extend({
    tableName: 'address',
    idAttribute: 'id',
    store: function(){
        return this.belongsTo(Store,'store_id');
    },
    city: function(){
        return this.belongsTo(City,'city_department');
    }
}

var City = bookshelf.Model.extend({
    tableName: 'city',
    idAttribute: 'department', //this is a composite pk (department and province)
    addresses: function(){
        return this.hasMany(Address,'city_department');
    }
}

And this is how I fetch one store (with it respective address and city):

return new Store({
        'id': 1
    })
    .fetch({
        withRelated: ['addresses.city']
    }).then(function(stores){
         res.send(stores.toJSON());
    );

Debug:

{ method: 'select',
  options: {},
  bindings: [ 1, 1 ],
  sql: 'select `boutique`.* from `boutique` where `boutique`.`id` = ? limit ?' }
{ method: 'select',
  options: {},
  bindings: [ 1 ],
  sql: 'select `address`.* from `address` where `address`.`boutique_id` in (?)' }
{ method: 'select',
  options: {},
  bindings: [ 15 ],
  sql: 'select `city`.* from `city` where `city`.`department` in (?)' 

And the result :

{
    "id": ​1,
    "name": "Store 1",
    "addresses": 
    [
        {
            "id": ​2,
            "address": "Store 1, Av 1",
            "store_id": ​1,
            "city_department": ​15,
            "city_province": ​1,
            "city": {
                "department": 15,
                "province": 0,
                "name": "Department 15"
            }
        },
        {
            "id": ​3,
            "address": "Store 1, Av 2",
            "store_id": ​1,
            "city_department": ​15,
            "city_province": ​1,
            "city": {
                "department": 15,
                "province": 0,
                "name": "Department 15"
            }
        }
    ]
}

As you can see when I use withRelated: ['addresses.city'] it queries just department on the whereclause . And I need to query department and province so I can see it related city.

As a mention before I've try to do it with query builder, but from all examples I could find it just use stactic values.

return new Store()
    .fetchAll({
        withRelated: ['addresses', {'addresses.city': function(qb){
            qb.where('province',1);}}] //Here I use 1 just to see if it work, and it does, but I need it to be dynamic according from the address.city_province 
    }).then(function(stores){
         res.send(stores.toJSON());
    );

This is where I'm stuck, and I can't seem to find a way to do it, unless the only way it's using a raw query with knex.

[rhys-vdw]

I'm not sure if this will work, but you could try this:

var City = bookshelf.Model.extend({
    tableName: 'city',
    idAttribute: 'department', //this is a composite pk (department, province)
    addresses: function(){
        var province = this.get('province');
        return this.hasMany(Address,'city_department').where('city_province', province);
    }
}

I suspect that will fail in some (hopefully not all) cases...

[ghost]

Hi @rhys-vdw, that works if after I fetch all Stores (with related address, not cities) and then loop through each address and fetch each city, which seems more work than just do it Knex. I was hoping it was a simpler way to do it with Bookshelf. Thanks for the help.

[ricardograca]

Closing this due to lack of activity. If you have any further info to add we can reopen this issue.