问题描述:

I am using Node.js with the mysql module and trying to figure out how I can search for all instances in a table where two users share a similar tag. A quick demo of my table would be:

username | tag

-------------------

jennifer | coffee

jennifer | pizza

jennifer | travel

joe | sushi

joe | bowling

joe | coffee

joe | travel

celine | singing

celine | pizza

celine | programming

So for example I would like to find what the tags are that joe and jennifer have in common which in this case would be two: coffee and travel. What is the best way to search for the tags they have in common? Any advice would be helpful. Thank you in advance!

网友答案:

this will find common tags providing 2 names:

SELECT tag
FROM yourTable
WHERE username in ("jennifer", "joe")
GROUP BY tag
HAVING count(*) > 1
网友答案:

Should be simple enough:

connection.query('SELECT username, tag from my_table where user="joe" or user="jennifer"', function(err, rows, fields) {
  if (err) throw err;

  // Reduce results to a list of Joe's tags
  const joesTags = rows.filter(function(row) {
    return row.username === 'joe';
  }).map(function (row) {
    return row.tag;
  });

  // Determine common tags by filtering to tags of Jennifer's that are also in Joe's list
  const commonTags = rows.filter(function(row) {
    return row.username === 'jennifer' && joesTags.indexOf(row.tag) !== -1
  });
});

I'm sure this isn't the most performant way to do it, but it should work.

Terser example taking advantage of ES6 syntax (requires Node 4.x or higher):

connection.query('SELECT username, tag from my_table where user="joe" or user="jennifer"', function(err, rows, fields) {
  if (err) throw err;

  // Reduce results to a list of Joe's tags
  const joesTags = rows.filter(row => row.username === 'joe').map(row => row.tag);

  // Determine common tags by filtering to tags of Jennifer's that are also in Joe's list
  const commonTags = rows.filter(row => row.username === 'jennifer' && joesTags.indexOf(row.tag) !== -1);
});
相关阅读:
Top