Objection.js: Parsing knex.raw() result to a model

[roytz]

Hi,
Is there a way calling parsing knex.raw() result to a specific model (the query I run returns a specific model)?

More so, if I call a postgres function (I created) using knex.raw(), can I get the result "objectified" to a clear json and not having to go through all the object 'rows'?

Thanks

[koskimas]

Could you give an example of how you are trying to use it?

[roytz]

Sure, I call knex.raw('select some_function()') which call a postgres function I created that does some work but eventually returns rows of known (and "modelized") object.

[koskimas]

So you are asking if objection modifies the output of knex.raw? The answer is no. Objection in no way monkey patches knex.raw. If you have a question about the output of knex.raw you should probably ask this question in knex forums or github.

[newhouse]

Perhaps your best bet is to use fromJson somewhere/somehow to create Model instances from the results of that function.

[roytz]

@koskimas it seems like you didn't understand my question.. In no place I requested for a monkey patch but a simple objet conversion from the knex.raw() results.

@newhouse thank you. I guess that's what I'll do.
It would have been nice if the the same way we use objection to create models from the db schemas we could use it to create models using the db functions (if possible).

[elhigu]

@roytz how about you call

Model.query().select(Model.raw('some_function()')).toString();
// outputs: 'select some_function()'

to be able to make query through objection instead of directly calling knex? I have no idea what kind of result object you will get like that though...

[shai32]

@elhigu: I tried that, not working

[Kostanos]

Anyone found the solution? I'm looking for the same. Thank you.

[Kostanos]

Ja, I think I got it, if someone need the same:

myModel.raw('my query', ...).then(data => {
  return myModel.fromDatabaseJson(data.rows.length ? data.rows[0] : null);
});

This works well for cases where you know which Model you exactly need.

[nicosierra]

What about setting an auxiliary static method that calls the original static raw and parses the result?
_(please note that this is meant for mysql SELECT statements exclusively, so I put it in a base model class for such purposes)_

  static rawModel(query, ...params) {
    return super.raw(query, ...params).then(data => {
      const results = data[0]; // this and the next three lines are specific to mysql
      return results.length > 1
        ? data[0].map(value => super.fromDatabaseJson(value))
        : super.fromDatabaseJson(data[0][0]);
    });
  }

Despite this, I don't see that the results are treated the same way as the rest of the model methods do (e.g. the knexSnakeCaseMappers is not used).

[nicosierra]

ok.. regarding my former post, the reason why the knexSnakeCaseMappers is not applied is because obviously this is part of the query builder from where all necessary info about columns is fetched beforehand (and is part of the knex logic, nothing to do with Objection).
Fortunately, Objection exposes the snakeCaseMappers, so I find a way of returning a model from a raw query by using @Kostanos answer and a bit of inheritance, so you can have the method available for all child classes. (of course if you don't want this type of transformation, just remove it).

const { Model, snakeCaseMappers } = require('objection');

const { parse, format } = snakeCaseMappers();

class BaseMySQLModel extends Model { // this implementation is for mysql
  static modelRawSelect(query, ...params) {
    return super.raw(query, ...params).then(data => {
      const results = data[0];
      return results.length > 1
        ? results.map(value => parse(super.fromDatabaseJson(value)))
        : parse(super.fromDatabaseJson(results[0]));
    });
  }
}

module.exports = BaseMySQLModel;

[iby]

@nicosierra Thanks for posting this! Though, you probably want to parse the result super.fromDatabaseJson(parse(value)), not the model parse(super.fromDatabaseJson(value)).