SELECT DISTINCT in Smart Segment

Help me!
1

Hello,

Is it possible to create a segment that will return only the values from a SELECT DISTINCT query ?

It keeps returning all the values…

I’m running with Postgres.

Thank you

2

Hi @Simon_BRAMI1 :wave: according to the documentation you can make a request like below inside the where property.

const { amodel } = require('../models');
where: (record) => {
  return amodel.findAll({
    attributes: ['id'],
    distinct: true,
  }).then(records => {
    const ids = records.map(({ id }) => id);
    return { id: { [Op.in]: ids } };
  });
}

Let me know :pray:

3

This could probably work if I had only one PK but unfortunately I have an other PK…

I have 'id' and 'hash' that makes every row unique, but I want to Select Distinct based on 'hash'.

So I tried this:

return models.table.findAll({
  attributes: [
    [Sequelize.fn('DISTINCT', Sequelize.col('hash')) ,'hash']
  ],
  distinct: true,
}).then(records => {
  const hashes = records.map(({ hash }) => hash);
  return { hash: { [Op.in]: hashes } };
});

But it will return all the rows that contains the hashes (which are not unique) instead of returning only one of them :pensive:

4

Just found a way to do it :rocket:

let res = await models.connections.default.query(`
  select "id", "hash"
  from (
    select *,
            ROW_NUMBER() over (partition by "hash" order by "created_at" desc) as row
    from table) as rows

  where rows.row = 1
`, { type: QueryTypes.SELECT })


var condition = res.map(val => {
  return {
    [Op.and]: [
      { hash: val.hash },
      { id: val.id }
    ]
  }
})
  
return { [Op.or]: condition}