Home

Index

How to use the fn function from sequelize

Find comprehensive JavaScript sequelize.fn code examples handpicked from public code repositorys.

sequelize.fn is a function provided by the Sequelize ORM that allows you to call SQL functions within your queries.

heroku.js

8
9
10
11
12
13
14
15
16
17
Order: {
  UserId: '(SELECT COUNT(*) FROM Orders WHERE Orders.UserId = User.id)'
},
char: {
  date: [sequelize.fn('date_format', sequelize.fn('convert_tz', sequelize.col('require_date'), '+00:00', timeZoneOffset), '%Y%c%d'), 'date'],
  time: [sequelize.fn('date_format', sequelize.fn('convert_tz', sequelize.col('require_date'), '+00:00', timeZoneOffset), '%H:%i'), 'time'],
  date_for_dashboard: [sequelize.fn('date_format', sequelize.fn('convert_tz', sequelize.col('require_date'), '+00:00', timeZoneOffset), '%m/%d'), 'date'],
  date_for_admin_dashboard: [sequelize.fn('date_format', sequelize.fn('convert_tz', sequelize.col('createdAt'), '+00:00', timeZoneOffset), '%m/%d'), 'date'],
},
geo: {

+ 7 other calls in file

project.js

GitHub: osmlab/to-fix-backend

93
94
95
96
97
98
99
100
101
102
  }
],
attributes: [
  'tags.name',
  'status',
  Sequelize.fn('COUNT', Sequelize.col('item.id'))
],
where: {
  project_id: projectId
},

+ 13 other calls in file

How does sequelize.fn work?

sequelize.fn is a function provided by the Sequelize library that allows you to use SQL functions in your queries, with the first argument being the name of the function and the subsequent arguments being the function's parameters. When executed, it generates the corresponding SQL query with the function applied to the specified columns or values.

account-adj.js

GitHub: fioprotocol/fio-registrations

1
2
3
4
5
6
7
8
9
10

module.exports = (sequelize, DataTypes) => {
  const AccountAdj = sequelize.define('AccountAdj', {
    created: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.fn('now'),
      allowNull: false,
      index: true
    },
    created_by: {

+ 3 other calls in file

repository.js

GitHub: ZePedroResende/torrentio-scraper

60
61
62
63
64
65
66
67
68
69
name: 'files_unique_file_constraint',
fields: [
  col('infoHash'),
  fn('COALESCE', (col('fileIndex')), -1),
  fn('COALESCE', (col('imdbId')), 'null'),
  fn('COALESCE', (col('imdbSeason')), -1),
  fn('COALESCE', (col('imdbEpisode')), -1),
  fn('COALESCE', (col('kitsuId')), -1),
  fn('COALESCE', (col('kitsuEpisode')), -1)
]

+ 13 other calls in file

Ai Example

1
2
3
4
5
6
7
8
9
10
11
const { Op, fn, col } = require("sequelize");
const User = require("./models").User;

const users = await User.findAll({
  attributes: [[fn("COUNT", col("id")), "userCount"]],
  where: {
    createdAt: {
      [Op.gte]: new Date("2022-01-01"),
    },
  },
});

In this example, sequelize.fn is used to count the number of users created after January 1st, 2022. The fn method is called with the string 'COUNT' to specify that we want to use the SQL COUNT function, and the col method is used to reference the id column in the User model. Finally, the alias userCount is used to rename the resulting column to userCount.

event.controller.js

GitHub: junagao/trash-walk-backend

157
158
159
160
161
162
163
164
165
166

// Compute Shape, Area and Distance based on coords
let geometry = await models.Location.findAll({
  attributes: [
    [
      Sequelize.fn(
        'ST_AsGeoJSON',
        Sequelize.fn(
          'ST_Buffer',
          Sequelize.fn('ST_MakeLine', Sequelize.col('geography')),

+ 19 other calls in file

locations.js

GitHub: Geo-ARG/Alternate-Reality-Game

17
18
19
20
21
22
23
24
25
26
include: [
  {model: models.Users}
],
attributes: {
  include: [[
    sequelize.fn('ST_DWithin',
    sequelize.col('geolocation'),
    sequelize.literal(`ST_Point(${req.body.latitude}, ${req.body.longitude})::geography`),
    1000), 'nearby'
  ]]

product.js

GitHub: purwadhikafullstack/JCWDOL00802

67
68
69
70
71
72
73
74
75
76
[
  sequelize.fn("sum", sequelize.col("prodtrans.total_item")),
  "jumlah",
],
[
  sequelize.fn(
    "sum",
    sequelize.col("prodtrans.total_purchased_price")
  ),
  "total_biaya",

+ 2 other calls in file

userController.js

GitHub: dannavhdiaz/OneStep-Shoes-PI_DH

167
168
169
170
171
172
173
174
175
176
    first_name: req.body.name,
    last_name: req.body.lastName,
    email: req.body.email,
    avatar_img: newImage,
    is_active: 1,
    modified_date: Sequelize.fn('NOW')
}, {
    where: {
        id: req.params.id
    }

+ 5 other calls in file

productController.js

GitHub: dannavhdiaz/OneStep-Shoes-PI_DH

295
296
297
298
299
300
301
302
303
304
    img: newImage,
    is_active: req.body.status,
    brand_id: parseInt(req.body.brand),
    category_id: parseInt(req.body.category),
    genre_id: parseInt(req.body.genre),
    modified_date: sequelize.fn('NOW')
},
{where: 
    {id:id}
})

+ 2 other calls in file

program.js

GitHub: daencis/e-monev

13
14
15
16
17
18
19
20
21
22

const search = []
const selection = [{status_id: 1}]
if(req.query.search && req.query.search !== null && req.query.search !== undefined && req.query.search !== ''){
    search.push({'$id$': Sequelize.where(
        Sequelize.fn('LOWER', Sequelize.col('id')), 'LIKE',
        `%${req.query.search.toLowerCase()}%`
    )})
    search.push({'$title$': Sequelize.where(
        Sequelize.fn('LOWER', Sequelize.col('title')), 'LIKE',

+ 53 other calls in file

activity.js

GitHub: daencis/e-monev

11
12
13
14
15
16
17
18
19
20
search.push({'$id$': Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col('id')), 'LIKE',
    `%${req.query.search.toLowerCase()}%`
)})
search.push({'$title$': Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col('title')), 'LIKE',
    `%${req.query.search.toLowerCase()}%`
)})
search.push({'$code$': Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col('code')), 'LIKE',

+ 2 other calls in file

user.js

GitHub: daencis/e-monev

42
43
44
45
46
47
48
49
50
51
search.push({'$id$': Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col('id')), 'LIKE',
    `%${req.query.search.toLowerCase()}%`
)})
search.push({'$username$': Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col('username')), 'LIKE',
    `%${req.query.search.toLowerCase()}%`
)})
search.push({'$name$': Sequelize.where(
    Sequelize.fn('LOWER', Sequelize.col('name')), 'LIKE',

+ 2 other calls in file

KidsController.js

GitHub: cakalangsous/solelandApi

44
45
46
47
48
49
50
51
52
53
            [Op.between]: [START.toISOString(), NOW.toISOString()],
        },
    },
    attributes: [
        [
            Sequelize.fn("sum", Sequelize.col("screen_time")),
            "total_screen_time_today",
        ],
    ],
})

+ 3 other calls in file

distributor.controller.js

GitHub: jatin-techteam/omct

281
282
283
284
285
286
287
288
289
  where: {
    stockhandeler: req.userId,
    status: { [Op.like]: `%instock%` },
  },
  group: ["modelid"],
  attributes: ["modelid", Sequelize.fn("count", Sequelize.col("modelid"))],
})
  .then((products) => {
    // console.log("counts", products.count);

+ 55 other calls in file

halal_cert.controller.js

GitHub: faizanrazashah/Brand-retailer-marketplace

77
78
79
80
81
82
83
84
85
86
  status: 1,
};
 Halal_cert.findAll({
    where: Sequelize.where(
      Sequelize.fn('lower', Sequelize.col('cert_name')), 
      Sequelize.fn('lower', req.body.cert_name)
    )
  })
    .then((data) => {
      if(!data.length)

+ 7 other calls in file

brand_product.controller.js

GitHub: faizanrazashah/Brand-retailer-marketplace

148
149
150
151
152
153
154
155
156
157
const newName = req.body.br_product_name.trim();
Brand_product.findAll({
  where: Sequelize.and(
    Sequelize.where(
      Sequelize.fn("lower", Sequelize.col("br_product_name")),
      Sequelize.fn("lower", newName)
    ),
    { status: 1 }
  ),
})

+ 11 other calls in file

retailer_type.controller.js

GitHub: faizanrazashah/Brand-retailer-marketplace

65
66
67
68
69
70
71
72
73
74
  status: 1,
};
Retailer_type.findAll({
  where: Sequelize.where(
    Sequelize.fn("lower", Sequelize.col("retailer_type_name")),
    Sequelize.fn("lower", req.body.retailer_type_name)
  ),
})
  .then((data) => {
    if (!data.length) {

+ 7 other calls in file

retailer_campaign.controller.js

GitHub: faizanrazashah/Brand-retailer-marketplace

168
169
170
171
172
173
174
175
176
177

const newName = req.body.ret_campaign_name.trim();

var data = await Retailer_campaign.findAll({
  where: Sequelize.where(
    Sequelize.fn("lower", Sequelize.col("ret_campaign_name")),
    Sequelize.fn("lower", newName)
  ),
});
//  .then((data) => {

+ 7 other calls in file

brand.controller.js

GitHub: faizanrazashah/Brand-retailer-marketplace

233
234
235
236
237
238
239
240
241
242
};

var data = await Brand.findAll({
  where: Sequelize.or(
    Sequelize.where(
      Sequelize.fn("lower", Sequelize.col("legal_name")),
      Sequelize.fn("lower", req.body.legal_name.trim())
    ),
    { br_email_address: req.body.br_email_address.trim() },
    { verify_code: randomnumber }

brand_category.controller.js

GitHub: faizanrazashah/Brand-retailer-marketplace

61
62
63
64
65
66
67
68
69
70
  status: 1,
};
Brand_category.findAll({
  where: Sequelize.where(
    Sequelize.fn("lower", Sequelize.col("br_category_name")),
    Sequelize.fn("lower", req.body.br_category_name)
  ),
})
  .then((data) => {
    if (!data.length) {

Other functions in sequelize

Sorted by popularity

sequelize.col is the most popular function in sequelize (1002 examples)

Other popular packages:

Home

About