title: Sequelize - scope with include
body: <p>I'm using Sequelize in my Node.js/Express app with the notion of scopes (<a href="http://docs.sequelizejs.com/en/latest/docs/scopes/" rel="nofollow">http://docs.sequelizejs.com/en/latest/docs/scopes/</a>).</p>
  
  <p>I would like to have two scopes on a Model. Each of these scope are based on a join of another table (see the two scopes <code>inLocation</code> &amp; <code>withExpertises</code>):</p>
  
  <pre><code>const Agency = sequelize.define("agency", {
      id:            { type: DataTypes.INTEGER, primaryKey: true },
      name:          DataTypes.STRING,
      address:       DataTypes.STRING,
      size:          DataTypes.INTEGER,
      description:   DataTypes.TEXT,
      logoFileName:  { type: DataTypes.STRING, field: "logo_file_name" },
      externalScore: { type: DataTypes.FLOAT, field: "external_score" }
    }, {
      classMethods: {
        associate(models) {
          Agency.belongsToMany(models.location, {
            through:    "locationsAgency",
            foreignKey: "location_id"
          });
          Agency.hasOne(models.locationsAgency);
          Agency.hasMany(models.service);
        }
      },
      scopes: {
        inLocation(locationId) {
          return {
            include: [{
              model: locationsAgency, where: { locationId: locationId }
            }]
          };
        },
        withExpertises(expertiseIds) {
          return {
            include: [{
              model: service, where: { expertiseId: expertiseIds }
            }]
          };
        },
        orderByRelevance: {
          order: '"externalScore" DESC'
        }
      }
    });
  </code></pre>
  
  <p>When I'm doing the following call:</p>
  
  <pre><code>models.agency.scope({ method: ["inLocation", 9260] },
                      { method: ["withExpertises", 79] },
                      "orderByRelevance")
               .findAll({ limit: 10 })
  </code></pre>
  
  <p>I have this SQL generated:</p>
  
  <pre><code>SELECT "agency".*, 
  "services"."id" AS "services.id", 
  "services"."expertise_id" AS "services.expertiseId"
  FROM (
      SELECT "agency".*, 
      "locationsAgency"."id" AS "locationsAgency.id",
      "locationsAgency"."location_id" AS "locationsAgency.locationId",
      "locationsAgency"."agency_id" AS "locationsAgency.agencyId" 
      FROM "agencies" AS "agency" 
      INNER JOIN "locations_agencies" AS "locationsAgency" ON "agency"."id" = "locationsAgency"."agency_id" 
      AND "locationsAgency"."location_id" = 9260 
      WHERE ( 
          SELECT "agency_id" 
          FROM "services" AS "service" 
          WHERE ("service"."agency_id" = "agency"."id" AND "service"."expertise_id" = 79) 
          LIMIT 1 
      ) 
      IS NOT NULL LIMIT 10
  ) AS "agency" 
  INNER JOIN "services" AS "services" ON "agency"."id" = "services"."agency_id" 
  AND "services"."expertise_id" = 79 
  ORDER BY "externalScore" DESC
  </code></pre>
  
  <p>As you can see, the scope are nested in the FROM-clause, which give me a awful SQL and then the <code>limit</code> statement is not a the good place.</p>
  
  <p>I expected to have this following SQL query:</p>
  
  <pre><code>SELECT "agency".*, 
  "services"."id" AS "services.id", 
  "services"."expertise_id" AS "services.expertiseId", 
  "services"."created_at" AS "services.created_at", 
  "services"."updated_at" AS "services.updated_at",
  "locationsAgency"."id" AS "locationsAgency.id", 
  "locationsAgency"."location_id" AS "locationsAgency.locationId", 
  "locationsAgency"."agency_id" AS "locationsAgency.agencyId"
  FROM "agencies" AS "agency"
  INNER JOIN "locations_agencies" AS "locationsAgency" ON "agency"."id" = "locationsAgency"."agency_id" 
  INNER JOIN "services" AS "services" ON "agency"."id" = "services"."agency_id" 
  AND "locationsAgency"."location_id" = 9260 
  AND "services"."expertise_id" = 79 
  ORDER BY "external_score" DESC
  LIMIT 10
  </code></pre>
  
  <p>Any ideas how to have this sql with the use of scopes?</p>
  
  <p>Thanks!</p>