在sequelize中，如何选择匹配多行不同值的记录？

As an example, I have the following table:

举个例子,我有下表:

A | B
------
1 | 2
1 | 3
1 | 4
2 | 3
2 | 4
3 | 3

I want to select all values in B that have the value 1 AND 2 in the A Column. So in the above example I should get as a result (3,4) because only 3 and 4 have for column A the values 1 and 2.

我想选择B中A列中值为1和2的所有值。所以在上面的例子中我应该得到结果(3,4)因为只有3和4对于A列有值1和2。

How would I do this in sequelize?

我怎么会在续集中这样做?

This is what I tried:

这是我试过的:

 db.myModel.findAll({
    where: {
      A: whatImSearching
    },
    attributes: ['A', 'B']
  })

with whatImSearching = [1,2]

with whatImSearching = [1,2]

But that solution returns results where only one of the values match, i.e. I would get as a result (2,3,4). But I should only get (3,4).

但是该解决方案返回只有一个值匹配的结果,即我得到的结果(2,3,4)。但我应该得到(3,4)。

2 个解决方案

#1

Note: This is how it can be done in SQL, like @daf mentioned in comments, something on similar lines can be implemented in Sequelize. I've kept this around as a reference

注意:这是如何在SQL中完成的,比如注释中提到的@daf,类似行中的内容可以在Sequelize中实现。我把它作为参考

You can use group by with conditional having like this

你可以像这样使用有条件的group by

SELECT B 
FROM Table1 GROUP BY B
HAVING SUM(CASE WHEN A IN(1,2) THEN 1 ELSE 0 END) = 2

EDIT

Assuming your have duplicate values. this should be better

假设您有重复的值。这应该会更好

SELECT B 
FROM Table1 GROUP BY B
HAVING SUM(CASE WHEN A = 1 THEN 1 ELSE 0 END) = 1
    AND SUM(CASE WHEN A = 2 THEN 1 ELSE 0 END) = 1

#2

Edit: I'm not immediately seeing a pure Sequelize way to do this, however the below should get where you want with the small hit of bringing back some extra rows from the database.

编辑:我没有立即看到一个纯粹的Sequelize方法来做到这一点,但是下面应该得到你想要的东西,从数据库中带回一些额外的行。

Assuming that you're doing the query using a model like:

假设您正在使用如下模型进行查询:

var model = sequelize.define('model', { 
    A: { type: Sequelize.TEXT }, 
    B: { type: Sequelize.TEXT }
}, {
    tableName: 'SomeTable',
    timestamps: false
})

you can do:

你可以做:

var search = [1,2];
model.findAll({
    where: { A: { '$in': search } },
    attributes: ['A', 'B']
}).then(function(instances) {
  var unwrapped = instances.map(function(i) { 
    return i.get();
  });
  var withCounts = unwrapped.reduce(function(acc, cur) {
    var thisB = cur.B;
    var accItem = acc.filter(function(i) { 
      return i.B === thisB; 
    })[0];
    if (!accItem) {
      accItem = { B: thisB, count: 0 };
      acc.push(accItem);
    }
    accItem.count++;
  }, [])
  var hasAll = withCounts.filter(function(i) {
    return i.count === search.length;
  }).map(function(i) {
    return i.B;
  });
})

#1