Unknown column in field list

[useo6]

What are you doing?

I'm executing the following query using Sequlize:

db.models.User.findAll({
      order: [['id', 'desc']],
      limit: 10,
      attributes: {
        exclude: ['id']
      },
      include: [{
        as: 'sessions',
        required: true,
        model: db.models.Session,
        include: [{
          as: 'currentSession',
          model: db.models.CurrentSession, // 1:1 association
          attributes: []
        }],
        attributes: {
          exclude: ['id'],
          include: ['currentSession.activeSince']
        }
      }]
    })

What do you expect to happen?

The field 'currentSession.activeSince' should be inclued in the attributes of the appropriate Session.

What is actually happening?

But instead of this, I'm getting the following error:

Unknown column 'sessions.currentSession.activeSince' in 'field list'

I figured out that the problem is that sequelize creates a query and references the activeSince-field as follows:

sessions.currentSession.activeSince

But the correct field would be:

sessions->currentSession.activeSince

Environment

Dialect:

• mysql
• postgres
• sqlite
• mssql
• any
  Dialect library version: 1.6.5 (mysql2)
  Database version: 5.7.21
  Sequelize version: 5.10.1
  Node Version: 12.6.0
  OS: Windows 10

[useo6]

I updated Sequelize to version 5.12.2 but the problem is persists.

[papb]

I didn't know sequelize supported this syntax include: ['currentSession.activeSince'] for nested attribute inclusion. Can you link to the docs where this is mentioned?

[useo6]

Wow, good question. Never checked out the documentation regarding this topic. I found many examples on Stack Overflow and many of these were accepted. So I thought this is supported by Sequelize. For example, on https://stackoverflow.com/questions/37817808/counting-associated-entries-with-sequelize the number of associated entries is counted by referencing a column using this way...

[papb]

Interesting, can you see if you can make it work in a super simple example? Yours didn't work, but perhaps you can find another simpler example, without includes, just to see it working.

[useo6]

What do you mean without includes? The includes are the problem because it seems that Sequelize creates a buggy query...

[papb]

I mean, does the ['currentSession.activeSince'] syntax work at all, at any situation whatsoever? Can you demonstrate it working in any situation, disregarding your issue for a moment?

[useo6]

I did some test again and simplified everything a bit and yes, it works, but let's see...

The following query works correctly:

  const test1 = await db.models.Session.findOne({
    raw: true
    where: {
      id: 1234,
    },
    attributes: ['currentSession.activeSince'],
    include: [{
      as: 'currentSesion',
      model: db.models.CurrentSession,
      attributes: []
    }]
  })
  console.log(test1)

This prints the following:

{ activeSince: '2019-05-09T03:48:36.000Z' }

So everything is fine and works as expected. So this proves that the discussed syntax works. But when I remove raw: true from the query, I'm getting an empty object:

  const test2 = await db.models.Session.findOne({
    where: {
      id: 1234,
    },
    attributes: ['currentSession.activeSince'],
    include: [{
      as: 'currentSesion',
      model: db.models.CurrentSession,
      attributes: []
    }]
  })
  console.log(test2.toJSON())

It doesn't throw any error or rejection, but it simply prints an empty object:

{}

Edit:\ When I execute the query of test2 generated by Sequelize directly on the database, I get a valid result (the same as for test1).

[papb]

Understood!

Now, I understood the problem, but your code snippets are not self-contained (i.e. I can't just copy-paste it and run them). Can you (or someone else) please provide a SSCCE (also known as MCVE/reprex)? This will help tackling the issue faster.

[jeanniton-mnr]

How to load attributes from the associated model in sequelize.js :
https://stackoverflow.com/questions/59941122/how-to-load-attributes-from-associated-models-with-sequelize-js

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[ihtishamKhan]

I have still the issue, every time when I want to find the avg of the column in the included model

Appointment
    .findAll({
        where: {
            patientId: req.params.patientId,
         },
         include: [{
         model: Doctor,
         as: 'doctor',
         attributes: ['id', 'firstName', 'middleName', 'lastName', 'profilePicture',
             // avg of ratings
             [sequelize.fn('AVG', sequelize.col('doctorRatings.rating')), 'rating'],
          ],
          include: [{
              as: 'doctorRatings',
              model: DoctorRating,
              required: false,
              attributes: [],
          }],
        }, {
            as: 'patient',
            model: Patient,
            attributes: ['id', 'firstName', 'middleName', 'lastName', 'profilePicture', ],
            required: false,
        }],
        })
        .then(appointments => {
            if (!appointments) {
                return res.status(404).send({
                    message: 'No Appointments Found',
                });
            }
            return res.status(200).send(appointments);
        })
        .catch(err => {
            return res.status(500).send({
                message: 'Internal Server Error',
            });
        });

[ephys]

@ihtishamKhan it would help if you could provide additional information like:

• Your Sequelize version
• Which dialect you're using
• What SQL this query is generating

[ihtishamKhan]

• Sequelize: ^6.12.0-alpha.1
• mysql2: ^2.3.3

Sequelize make it easy to query data, but I don't know why it not working normally, maybe I am missing something or what
Now I have also another issue, I included a count function on related model, and that results in showing just one record instead of all