Monday, 21 September 2015

Sails waterline ORM on an MySQL view

I am required to keep a track of the number of selections a user has made so that when they are below a particular number they can be contacted.

To do this I need to monitor a group by with having count(*) query, for which I decided to generate a view:

create view progress_view as select user.username, display_name, email, year_of_entry, count(*) as num_choices from user left join selection on user.username = selection.username where progress = 1 group by username;

unfortunately defining a regular sails model object fails to load as it complains that we are 'Trying to define a collection (progress_view) which already exists.'

to get around this and to prevent a regular query attempting to retrieve the default fields createdAt, updateAt, id when using the .find() query on the table the following was wadded to the model definition:

var Progress = {
  // Enforce model schema in the case of schemaless databases
  schema: true,

  autoPK: false,
  autoCreatedAt: false,
  autoUpdatedAt: false,
  tableName: 'progress_view',
  migrate: 'safe',

  attributes: {
    username  : { type: 'string', unique: true },
    email     : { type: 'email' }, //,  unique: true },
    display_name     : { type: 'string' }, //,  unique: true },
year_of_entry : { type: 'integer' }, //only on some will be added -> or maybe all, but generally in the inst_name?
num_choices : { type: 'integer' }, //note the minimum here is always 1! evern if the student has not chosen on (as we are identifying with left join)

module.exports = ProgressProgress;

hurrah - we can query our view!

No comments:

Post a Comment