query produces ambigious column reference when using attributes and include

[User4martin]

The following example fails with

Unhandled rejection SequelizeDatabaseError: column reference "name" is ambiguous

var S = require('sequelize');

var sequelize = new S('seq_test', 'postgres', '', {
//var sequelize = new S('seq_test', 'passwd', '', {
    host: 'localhost',
    dialect: 'postgres',
    pool: { max: 15, min: 0, idle: 10000, },
    define: {  },
});

var User = sequelize.define('User', {
    name: { type: S.STRING(50), allowNull: true},
    pass: { type: S.STRING(50), allowNull: true},
});
var Foo = sequelize.define('Foo', {
    name: { type: S.STRING(50), allowNull: true},
    pass: { type: S.STRING(50), allowNull: true},
});

User.belongsTo(Foo, {});

sequelize.sync()
.then(function (){
    User.findAll({
        //raw: true,
        attributes: [  [ S.col('name'), 'username' ]   ],
        include: [
            {   model: Foo,
                attributes: [ [ S.col('name'), 'fooname' ] ],
            },
        ],
    });
});

sql produced.

SELECT 
  "name" AS "username", 
  "Foo"."name" AS "Foo.fooname" 
FROM "Users" AS "User" 
LEFT OUTER JOIN "Foos" AS "Foo" ON "User"."FooId" = "Foo"."id";

as you can see the S.col('name') in the User relation did not prefix the column with the table alias.

If there is no "attributes" section, and all columns are used then columns from the user relation are prefixed.

[janmeier]

I realize this might just be a small, contrieved example, but there is no reason to use .col in this example. attributes: ['username'] will work fine, including aliasses.

.col is used in combination with other utilities, for example if you want to say sequelize.fn('upper', sequelize.col('soemthing') in order to tell sequelize that the arguemnt to the UPPER functiion is a column, not a string and should be escaped as such.

You could write .col('table.column') , although I realized this is not ideal, especially not when the same table can be included multiple times, under several alisses

[User4martin]

Thanks for the feedback, and yes it also happens in s.fn().

Prefixing with the table name would work. Except I am generating the attributes from other data, so I need to write my own code to generate the same prefix, as sequelize does. Is it guaranteed never to change? See #4315 it might have to change in order to fix this.

[User4martin]

not stale

[User4martin]

not stale

[mrlubos]

Not stale.

[jackson-sandland]

still an issue...bueller

when using the table.col notation I'm seeing: "invalid reference to FROM-clause entry for table \"table\""

[eassymo]

any update?

[1valdis]

Any update? I can't properly cast a column of included model because of this.

[PatrickGeyer]

Anyone still interested in a solution here? I am.

[1valdis]

Yep.

[1valdis]

Although we use Sequelize in one of our projects, we've started another one on TypeORM. So maybe won't even have to wait. Will see and compare which one is better...

[je-poy]

Try this one attributes: [fn('trim', col('table1->table2->table3.col_name')), 'col_alias']

[papb]

Indeed this is still an issue, here is a simplified SSCCE for reproduction:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:');

const User = sequelize.define('User', { name: Sequelize.STRING });
const Foo = sequelize.define('Foo', { name: Sequelize.STRING });

User.belongsTo(Foo, {});

sequelize.sync().then(function() {
    User.findAll({
        attributes: [[ Sequelize.col('name'), 'username' ]],
        include: {
            model: Foo,
            attributes: [[ Sequelize.col('name'), 'fooname' ]],
        }
    });
});

SQL:

SELECT
    `User`.`id`,
    `name` AS `username`,
    `Foo`.`id` AS `Foo.id`,
    `Foo`.`name` AS `Foo.fooname`
FROM `Users` AS `User`
LEFT OUTER JOIN `Foos` AS `Foo` ON `User`.`FooId` = `Foo`.`id`;

[je-poy]

Try to add the table name on your Sequelize.col and use it on its singular form. Also try to add "->" if it is nested.

sequelize.sync().then(function() {
    User.findAll({
        attributes: [[ Sequelize.col('User.name'), 'username' ]],
        include: {
            model: Foo,
            attributes: [[ Sequelize.col('User->Foo.name'), 'fooname' ]],
        }
    });
});```

[diegohideky]

I had the same problem, and I could fix this easily by enabling the flag subQuery to true

User.findAll({
        //raw: true,
        attributes: [  [ S.col('name'), 'username' ]   ],
        include: [
            {   model: Foo,
                attributes: [ [ S.col('name'), 'fooname' ] ],
            },
        ],
        subQuery: true, <-------------------------------
    });

[FantasyNeurotic]

@papb I suggest we can set the alias name when use the function of find.e:

const aliasName = 'xx'
const instance = await model.findAll({ as: aliasName where: {}, order: [[Sequelize.fn('convert_to', `${aliasName}.${name}`, 'gbk'), 'asc']]})

[bhavin-bhuva]

I had the same problem, and I could fix this easily by enabling the flag subQuery to true

User.findAll({
        //raw: true,
        attributes: [  [ S.col('name'), 'username' ]   ],
        include: [
            {   model: Foo,
                attributes: [ [ S.col('name'), 'fooname' ] ],
            },
        ],
        subQuery: true, <-------------------------------
    });

thank you bro worked for me

[yogithesymbian]

not work message : "Unknown column 'user.dm_personal->user.full_name' in 'field list'" and subQuery , its not work ...

then

// group: ["id"], // we need define this when using subQuery,  right ? its broken result expected to pagination mode
      // subQuery: true,