权限没有,则隐藏 function forum_list_access_filter($forumlist, $gid, $allow = 'allowread') { global $grouplist; if (empty($forumlist)) return array(); if (1 == $gid) return $forumlist; $forumlist_filter = $forumlist; $group = $grouplist[$gid]; foreach ($forumlist_filter as $fid => $forum) { if (empty($forum['accesson']) && empty($group[$allow]) || !empty($forum['accesson']) && empty($forum['accesslist'][$gid][$allow])) { unset($forumlist_filter[$fid]); } unset($forumlist_filter[$fid]['accesslist']); } return $forumlist_filter; } function forum_filter_moduid($moduids) { $moduids = trim($moduids); if (empty($moduids)) return ''; $arr = explode(',', $moduids); $r = array(); foreach ($arr as $_uid) { $_uid = intval($_uid); $_user = user_read($_uid); if (empty($_user)) continue; if ($_user['gid'] > 4) continue; $r[] = $_uid; } return implode(',', $r); } function forum_safe_info($forum) { //unset($forum['moduids']); return $forum; } function forum_filter($forumlist) { foreach ($forumlist as &$val) { unset($val['brief'], $val['announcement'], $val['seo_title'], $val['seo_keywords'], $val['create_date_fmt'], $val['icon_url'], $val['modlist']); } return $forumlist; } function forum_format_url($forum) { global $conf; if (0 == $forum['category']) { // 列表URL $url = url('list-' . $forum['fid'], '', FALSE); } elseif (1 == $forum['category']) { // 频道 $url = url('category-' . $forum['fid'], '', FALSE); } elseif (2 == $forum['category']) { // 单页 $url = url('read-' . trim($forum['brief']), '', FALSE); } if ($conf['url_rewrite_on'] > 1 && $forum['well_alias']) { if (0 == $forum['category'] || 1 == $forum['category']) { $url = url($forum['well_alias'], '', FALSE); } elseif (2 == $forum['category']) { // 单页 $url = ($forum['threads'] && $forum['brief']) ? url($forum['well_alias'] . '-' . trim($forum['brief']), '', FALSE) : url($forum['well_alias'], '', FALSE); } } return $url; } function well_forum_alias() { $forumlist = forum_list_cache(); if (empty($forumlist)) return ''; $key = 'forum-alias'; static $cache = array(); if (isset($cache[$key])) return $cache[$key]; $cache[$key] = array(); foreach ($forumlist as $val) { if ($val['well_alias']) $cache[$key][$val['fid']] = $val['well_alias']; } return array_flip($cache[$key]); } function well_forum_alias_cache() { global $conf; $key = 'forum-alias-cache'; static $cache = array(); // 用静态变量只能在当前 request 生命周期缓存,跨进程需要再加一层缓存:redis/memcached/xcache/apc if (isset($cache[$key])) return $cache[$key]; if ('mysql' == $conf['cache']['type']) { $arr = well_forum_alias(); } else { $arr = cache_get($key); if (NULL === $arr) { $arr = well_forum_alias(); !empty($arr) AND cache_set($key, $arr); } } $cache[$key] = empty($arr) ? '' : $arr; return $cache[$key]; } ?>sql - How to Add a Conditional custom Flag in Sequelize Query? - Stack Overflow
最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

sql - How to Add a Conditional custom Flag in Sequelize Query? - Stack Overflow

programmeradmin9浏览0评论

Problem: I am using Sequelize in a Node.js application, and I need to add a new field to my query result. The new field, watchListed, should be true if a Company has more than 1 related watchlist entries for a given user otherwise it should be false.

But it gives me this error:

** Error in get all companies TypeError: attr[0].includes is not a function at C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1085:76 at Array.map () at PostgresQueryGenerator.escapeAttributes (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1072:37) at PostgresQueryGenerator.selectQuery (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-generator.js:880:28) at PostgresQueryInterface.select (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-interface.js:407:59) at Company.findAll (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\model.js:1140:47) at runNextTicks (node:internal/process/task_queues:60:5) at process.processImmediate (node:internal/timers:449:9)

**

I am using Sequelize with Node.js, and here's the code I'm trying to run:

const result = await Company.findAndCountAll({
                where: filters,  
                limit: limit,  
                offset: skip, 
                order: order,
                include: [
                    {
                        model: Watchlist,
                        as: 'watchlists',
                        required: false,
                        where: {
                            user_id: user.id
                        },
                    }
                ], // Perfrom Join operation with watchlist model and compare logged in user's id
                attributes: ['id', 'company_name',
                    [
                        fn('IF', fn('COUNT', col('watchlists.id')), {
                            [Op.gt]: 1
                        }, true, false), 'watchListed'
                    ]
                ],
              // Tried solution with Sequelize.literal
              // attributes: [
                //     [
                //         literal(`CASE WHEN COUNT(watchlists.id) > 0 THEN 
                            true ELSE false END`),
                //         'watchListed',
                //     ],
                // ],
                // group: ['Company.id'],
            });

    // My company models and associations:
    class Company extends Model {
        static associate(models) {
            // Added the foreign key constraint for companyId
            Company.hasMany(models.Watchlist, {
                foreignKey: 'companyId',
                as: 'watchlists'
            });
        }
    }

Company.init({
    id: {
        type: DataTypes.STRING,
        defaultValue: uuid.v4(),
        primaryKey: true,
        allowNull: false,
    },
    company_name: {
        type: DataTypes.STRING,
        allowNull: false,
    }
    // ... Other companies attributes
}, {
    sequelize,
    modelName: 'Company',
    tableName: 'companies',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
});


// Watchlist model and associations

class Watchlist extends Model {
    static associate(models) {
      // Added the foreign key constraint for user_id
      Watchlist.belongsTo(models.User, {
        foreignKey: 'user_id',
        onDelete: 'CASCADE'
      });

      Watchlist.belongsTo(models.Company, {
        foreignKey: 'companyId',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
      });
    }
  }
  Watchlist.init({
    id: {
      type: DataTypes.STRING,
      defaultValue: () => uuid.v4(),
      primaryKey: true,
      allowNull: false,
    },
    user_id: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    companyId: {
      type: DataTypes.STRING,
      allowNull: false,
    }
  }, {
    sequelize,
    modelName: 'Watchlist',
    tableName: 'watchlists',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
  });

I have also tried to use parameterized queries by using replacements, but that hasn’t resolved the issue.

What I’ve Tried:

  • Using Sequelize.literal with raw SQL to add the watchListed column.

  • Attempted to use COUNT(*) with Sequelize.literal and a conditional expression.

  • Tried parameterizing the query to prevent SQL injection.

  • Attempted grouping by Company.id.

Expected Behavior:

  • The query should return the watchListed flag as true if the count of watchlists for that company exceeds 1 and false otherwise.

Environment:

  • Sequelize version: 6.37.5

  • Node.js version: v20.15.0

  • Database: PostgreSQL

发布评论

评论列表(0)

  1. 暂无评论