Блог

У меня есть следующий запрос, и я хочу написать его как запрос на продолжение

 `SELECT "Gigs"."id",
                        "gigType",
                        "gigCategory",
                        "gigTitle",
                        "gigDescription",
                        "minOrderAmount",
                        unit,
                        "unitPrice",
                        stock,
                        sold,
                        "expireDate",
                        "Gigs".userid                                                 AS "sellerId",
                        "growerType"                                                  AS "sellerType",
                        "points",
                        json_build_object('id', "locationId", 'lat', lat, 'lng', lng) AS location
                 FROM (SELECT DISTINCT ON ("gigid") "gigid", "locationId", lat, lng
                       FROM (SELECT "gigid",
                                    id                          as "locationId",
                                    st_x(coordinates::geometry) as lat,
                                    st_y(coordinates::geometry) as lng
                             FROM "Locations"
                             WHERE ST_DWithin(coordinates,
                                              ST_MakePoint(${location.lat}, ${location.lng})::geography,
                                              ${distance})
                             ORDER BY coordinates <-> ST_MakePoint(${location.lat}, ${location.lng})::geography
                             LIMIT ${limit}) AS nearGigIds) AS distinctGigIds
                          INNER JOIN "Gigs"
                                     ON distinctGigIds."gigid" = "Gigs"."id"
                          INNER JOIN "Users" U
                                     ON U.id = "Gigs".userid
                          INNER JOIN "Customers" C on U.id = C.userid
                          INNER JOIN "Growers" G on C.userid = G.userid
                 WHERE "expireDate" > ${today}::text::date
                 ORDER BY points DESC
                 OFFSET ${offset} LIMIT 10;`
 

Я хочу знать, как записать ПОРЯДОК ПО координатам <-> ST_MakePoint(${location.lat}, ${location.lng})::география<-> часть в запросе. Я сослался на документы, и есть способ написать функции по ПОРЯДКУ следующим образом.

   order: [
    // Will order by  otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  ],
});
 

Но я не понимаю, как написать вышеупомянутую часть таким образом.

 db.Table.findAll({
    attributes: {
        include: [
            [
                Sequelize.fn(
                    'ST_Distance',
                    Sequelize.fn('point', Sequelize.col('longitude'), Sequelize.col('latitude')),
                    Sequelize.fn('point', longitude, latitude),
                ),
                'distanceAttribute',
            ],
        ],
    },
    order:[['distanceAttribute', 'DESC']]
});