How to add reference field in a smart/virtual collection?

Help me!
1

Hi, I am trying to add a reference to another table in a smart collection:

collection('search_history_stats', {
  isSearchable: true,
  fields: [
    {
      field: 'query',
      type: 'String',
    },
    {
      field: 'count',
      type: 'Number',
    },
    {
      field: 'first_search',
      type: 'Number',
    },
    {
      field: 'village',
      type: 'String',
      reference: 'village.id',
    }
  ],
});

In the code of the get route I have that:

const searchHistoryStatsSerializer = new RecordSerializer({ name: 'search_history_stats' });

router.get('/search_history_stats', (req, res, next) => {
  const limit = parseInt(req.query.page.size) || 20;
  const offset = (parseInt(req.query.page.number) - 1) * limit;
  const queryType = QueryTypes.SELECT;
  let conditionSearch = '';

  if (req.query.search) {
    conditionSearch = `query ILIKE '%${req.query.search.replace(/\'/g, '\'\'')}%'`;
  }


  const queryData = `
   .....
  `;

  const queryCount = `
   .....
  `;


  Promise.all([
    models.sequelize.query(queryData, { type: queryType }),
    models.sequelize.query(queryCount, { type: queryType }),
  ])
    .then(async ([list, cnt]) => {

      console.log(list)

      const stats = await searchHistoryStatsSerializer.serialize(list);
      const count = cnt[0].count;
      res.send({ ...stats, meta: { count: count } });
    })
    .catch((err) => next(err));
})

Actual behavior

Currently, I have this log from the console.log(list)

[
  {
    query: 'hobby:913',
    count: '2',
    village: 2,
    first_search: 2020-12-29T10:26:22.288Z
  },
  {
    query: 'hobby:894',
    count: '1',
    village: 2,
    first_search: 2020-12-29T10:26:22.288Z
  }
]

And right after that, I got several errors about Cannot read property 'id' of undefined
(full logs here [forest] 🌳🌳🌳 Cannot set the meetings_total value because of an unexpected error - Pastebin.com)

All of these logs are about a computed field in the village collection. However, I don’t care about the smart fields in the village collection because I am fetching search_history_stats which is a virtual collection. I just want a reference that points to the village in the list of the search history stats virtual collection.

Expected behavior

No errors, the list shows my collection with a relationship with the village collection.

Failure Logs

  • Package Version: Package version of what ?
  • Express Version: ~4.17.1
  • Sequelize Version: ~5.15.1
  • Database Dialect: Postgres
  • Database Version: 12.4
  • Project Name: shakai
1 Like
2

Hi @alexis,

Your Smart Collection definition is missing an ID field, it’s mandatory to have one :slight_smile:

You MUST declare an id field when creating a Smart Collection. The value of this field for each record MUST be unique.

As we are using the customer id in this example, we do not need to declare an id manually.

Documentation - https://docs.forestadmin.com/documentation/reference-guide/collections/create-a-smart-collection

Cheers,

3

Thanks for your answer, the collection now is displayed but the village does not appear:

image
image1488×167 12.6 KB

And the logs are pretty the same, forest can’t compute village smart fields…

1 Like
4

Great! Can you try changing the above to :point_down:t3:
reference: 'villages.id',

and maybe show us the Smart Field computation done at the route level to check?

5

'village.id' Is the reference I am using in smart actions and it works like a charm :slight_smile:
I think the problem is elsewhere.

6

Hello @alexis ,

Have you tried what @anon20071947 suggested for the reference field? If it still doesn’t work, can you share your smart collection and route code with us so that we can reproduce it locally? :slight_smile: