Omit full table path in join query results.

[brettvitaz]

I am trying to omit the full table path (e.g. tableB.column) from results in a raw query using a join. Is there a mechanism for doing this, or a workaround? I believe that this is related to issue #4311.

I have tried aliasing the column, but it still added the prefix.

What you are doing?

I am executing a query that looks something like the following:

  TableA
    .findAll({
      attributes: ['id', 'name', 'other'],
      include: [{
        attributes: ['partner_id'],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

What do you expect to happen?

I'd like to see output that does not include tableB and resembles:

[
  {
    "id": 1,
    "name": "stuff",
    "other": true,
    "partner_id": 1,
  }
]

What is actually happening?

I am getting output that resembles:

[
  {
    "id": 1,
    "name": "stuff",
    "other": true,
    "tableB.partner_id": 1,
  }
]

Dialect: postgres
Database version: 9.6.2
Sequelize version: 3.30.4

[sushantdhiman]

With { raw: true } you will get whatever columns you have selected from database. You will have to loop through keys and remove prefix tableB yourself

[brettvitaz]

I have already implemented this and was afraid that it would be your answer. I would like to request that columns with a column alias be given the option to be specified explicitly and honored even in a join. This becomes a problem when dealing with consumers that expect a certain payload, because part of an api must be written in a non-standard way to compensates for this.

[sushantdhiman]

I agree, there should be a way to get aliased names, may be we can tackle it in #5708

[mjviljan]

I've managed to get this working by including the joined attributes in the main attributes section with the table name included.

I.e. instead of

TableA
    .findAll({
      attributes: ['id', 'name', 'other'],
      include: [{
        attributes: ['partner_id'],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

you could try

TableA
    .findAll({
      attributes: ['id', 'name', 'other', 'TableB.partner_id'],
      include: [{
        attributes: [],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

I think the fact this works might not be explicitly intended behavior, however, and might change in the future. But at least for me it's currently working.

Edit: I'm using this against a MySQL database and my Sequelize version is 3.30.4.

[mortenko]

I have same problem. I'm using findAndCountAll method and it concat me table name to column name. So response is really "bad" if you are using it with graphql...
It does not work for :
db: postgresql
sequelize: 4.37.10

[odravison]

I've managed to get this working by including the joined attributes in the main attributes section with the table name included.

I.e. instead of

TableA
    .findAll({
      attributes: ['id', 'name', 'other'],
      include: [{
        attributes: ['partner_id'],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

you could try

TableA
    .findAll({
      attributes: ['id', 'name', 'other', 'TableB.partner_id'],
      include: [{
        attributes: [],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

I think the fact this works might not be explicitly intended behavior, however, and might change in the future. But at least for me it's currently working.

Edit: I'm using this against a MySQL database and my Sequelize version is 3.30.4.

I have been trying to do this approach and I can't get the same result.

Perhaps because Sequelize has changed?! Can you get the same result yet?

[jscamara]

Hi,

This should do the trick,

TableA.findAll({
  attributes: ['id', 'name', 'other', [Sequelize.col('TableB.partner_id'), 'partner_id']],
  raw: true,
  include: [{
    model: TableB,
    required: true
  }]
})

[odravison]

Hi,

This should do the trick,

TableA.findAll({
  attributes: ['id', 'name', 'other', [Sequelize.col('TableB.partner_id'), 'partner_id']],
  raw: true,
  include: [{
    model: TableB,
    required: true
  }]
})

That's RIGHT dude, that's it!
This is what I'm looking for!

[kennethpdev]

I've managed to get this working by including the joined attributes in the main attributes section with the table name included.

I.e. instead of

TableA
    .findAll({
      attributes: ['id', 'name', 'other'],
      include: [{
        attributes: ['partner_id'],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

you could try

TableA
    .findAll({
      attributes: ['id', 'name', 'other', 'TableB.partner_id'],
      include: [{
        attributes: [],
        model: TableB,
        nested: false,
        required: true,
      }],
      raw: true,
    })

I think the fact this works might not be explicitly intended behavior, however, and might change in the future. But at least for me it's currently working.

Edit: I'm using this against a MySQL database and my Sequelize version is 3.30.4.

thanks for this!!