title: Sequelize - How to pull records with 1 associated record and order by associated record attribute
body: <p>I have a Convos table, which has many Messages.</p>
  
  <p>What I want: pull all convos and last message. Order the convos by <code>last_message.created_at</code></p>
  
  <pre><code>models.Convos.findAll({
      include: [
          {
              model: models.Messages,
              as: "last_message",
              order: [ [ 'created_at', 'DESC' ]],
              limit: 1,
           }
      ],
      where:{
          [Op.or]: [
              {
                  sender_id: req.decoded.id
              },
              {
                  recipient_id: req.decoded.id
              }
          ],
      },
  )}
  </code></pre>
  
  <p>The closest I've gotten to ordering is with :</p>
  
  <pre><code>order: [
    [{model: models.Messages, as: 'last_message'}, 'created_at', 'DESC'],
  ],
  </code></pre>
  
  <p>But this gives the error:</p>
  
  <pre><code>`Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "last_message"`
  </code></pre>
  
  <p>From here, I am guessing this error may allude that there are convos without any messages, making <code>last_message.created_at</code> undefined (I could be completely misunderstanding this error though).</p>
  
  <p>So, from there, I have been trying to add a clause to the <code>where</code> statement that only pulls convos that have at least 1 message. Here are a bunch of things I've tried, and they all throw an error:</p>
  
  <p>adding to where:</p>
  
  <pre><code>Sequelize.literal("`last_message`.`id` IS NOT NULL")
  
  
  '$models.Messages.id$': { [Op.ne]: null },
  
  Sequelize.fn("COUNT", Sequelize.col("last_message")): { [Op.gt]: 0 }
  '$last_message.id$': { [Op.ne]: null }
  
  '$last_message.id$': {
    [Op.ne]: null
  }
  </code></pre>
  
  <p>I've also tried <code>having</code> instead of a <code>where</code> statement: </p>
  
  <pre><code>having: Sequelize.where(Sequelize.fn('COUNT', Sequelize.col('$last_message.id$')), '&gt;=', 0)
  </code></pre>
  
  <p>How can I properly sort the convos by it's associated record, <code>last_message.created_at</code>?</p>
  
  <p>UPDATE - RELEVANT PARTS OF CONVOS MODEL</p>
  
  <pre><code>"use strict";
  module.exports = (sequelize, DataTypes) =&gt; {
    let Convos = sequelize.define(
      "Convos",
      {
        sender_id: {
          type: DataTypes.INTEGER,
          references: {
            model: "Users",
            key: "id"
          }
        },
        recipient_id: {
          type: DataTypes.INTEGER,
          references: {
            model: "Users",
            key: "id"
          }
        },
        created_at: DataTypes.DATE,
        updated_at: DataTypes.DATE
      },
      {
        timestamps: false,
        freezeTableName: true,
        schema: "public",
        tableName: "convos"
      }
    );
  
    Convos.associate = models =&gt; {
      Convos.hasMany(models.Messages, {
        as: "last_message",
        foreignKey: "convo_id",
        sourceKey: "id"
      });
    };
  
    return Convos;
  };
  </code></pre>
  
  <p><strong>UPDATE</strong>
  I've figured out the issue is using Sequelize.literal when the associated model has <code>limit</code>. For example, this works:</p>
  
  <pre><code>models.Convos.findAll({
    include: [
    {
          model: models.Messages,
          as: "last_message",
          order: [ [ 'created_at', 'DESC' ]],
          //limit: 1,
          required: true,
          duplicating: false, 
      },
  ],
  where: {
    [Op.or]: [
        {
            sender_id: req.decoded.id
        },
        {
            recipient_id: req.decoded.id
        }
    ],
  },
  order: [[Sequelize.literal(`last_message.created_at`), 'DESC']],
  offset: offset,
  limit: 10,
  }).then(convos =&gt; { ....
  </code></pre>
  
  <p>But when I uncomment the <code>limit: 1</code> in the include part, I get the error: 
  <code>Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "last_message"</code></p>
  
  <p>Here is the query logs without <code>limit 1</code>:</p>
  
  <pre><code>Executing (default): SELECT "Convos"."id", "Convos"."sender_id", "Convos"."recipient_id", "Convos"."created_at", "Convos"."updated_at", "last_message"."id" AS "last_message.id", "last_message"."body" AS "last_message.body", "last_message"."read" AS "last_message.read", "last_message"."group_meeting_id" AS "last_message.group_meeting_id", "last_message"."user_id" AS "last_message.user_id", "last_message"."created_at" AS "last_message.created_at", "last_message"."updated_at" AS "last_message.updated_at", "last_message"."convo_id" AS "last_message.convo_id", "last_message-&gt;user"."id" AS "last_message.user.id", "last_message-&gt;user"."first_name" AS "last_message.user.first_name", "last_message-&gt;user"."avatar_file_name" AS "last_message.user.avatar_file_name", "senderUser"."id" AS "senderUser.id", "senderUser"."first_name" AS "senderUser.first_name", "senderUser"."avatar_file_name" AS "senderUser.avatar_file_name", "recipientUser"."id" AS "recipientUser.id", "recipientUser"."first_name" AS "recipientUser.first_name", "recipientUser"."avatar_file_name" AS "recipientUser.avatar_file_name" FROM "public"."convos" AS "Convos" INNER JOIN "public"."msgs" AS "last_message" ON "Convos"."id" = "last_message"."convo_id" LEFT OUTER JOIN "public"."users" AS "last_message-&gt;user" ON "last_message"."user_id" = "last_message-&gt;user"."id" LEFT OUTER JOIN "public"."users" AS "senderUser" ON "Convos"."sender_id" = "senderUser"."id" LEFT OUTER JOIN "public"."users" AS "recipientUser" ON "Convos"."recipient_id" = "recipientUser"."id" WHERE ("Convos"."sender_id" = 32 OR "Convos"."recipient_id" = 32) ORDER BY last_message.created_at DESC LIMIT 10 OFFSET 70;
  </code></pre>
  
  <p>Query with <code>limit: 1</code>:</p>
  
  <pre><code>Executing (default): SELECT "Convos"."id", "Convos"."sender_id", "Convos"."recipient_id", "Convos"."created_at", "Convos"."updated_at", "senderUser"."id" AS "senderUser.id", "senderUser"."first_name" AS "senderUser.first_name", "senderUser"."avatar_file_name" AS "senderUser.avatar_file_name", "recipientUser"."id" AS "recipientUser.id", "recipientUser"."first_name" AS "recipientUser.first_name", "recipientUser"."avatar_file_name" AS "recipientUser.avatar_file_name" FROM "public"."convos" AS "Convos" LEFT OUTER JOIN "public"."users" AS "senderUser" ON "Convos"."sender_id" = "senderUser"."id" LEFT OUTER JOIN "public"."users" AS "recipientUser" ON "Convos"."recipient_id" = "recipientUser"."id" WHERE ("Convos"."sender_id" = 32 OR "Convos"."recipient_id" = 32) ORDER BY last_message.created_at DESC LIMIT 10 OFFSET 0;
  </code></pre>
  
  <p>Here are some links that were useful in understanding <code>limit</code> is causing the issue, but I have still not found a solution that solves this problem.</p>
  
  <p><a href="https://github.com/sequelize/sequelize/issues/8432" rel="noreferrer">Link 1</a></p>
  
  <p><a href="https://github.com/sequelize/sequelize/issues/9861" rel="noreferrer">Link 2</a></p>
  
  <p><a href="https://github.com/sequelize/sequelize/issues/9507" rel="noreferrer">Link 3</a></p>
  
  <p><a href="https://github.com/sequelize/sequelize/issues/4446" rel="noreferrer">Link 4</a></p>
  
  <p>Thanks!</p>