How parse data using join on Objection.js

Question

I have two models: Place and User. I need to display only the list of places which have status = true and the status of the users which is true and the name of the place match the string enter by a user.

class Place extends Model {

  static get tableName() {
    return 'place';
  }

  static get relationMappings() {
    return {
     user: {
        relation: Model.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'place.user_id',
          to: user.id',
        },
      },
    };

static get jsonSchema() {
    return {
      type: 'object',
      required: ['name'],

      properties: {
        id: { type: 'integer' },
        name: { type: 'string', minLength: 1, maxLength: 255 },
        user_id: { type: 'integer' },
        status: {type: boolean},
      },
    };
  }
  }

class User extends Model {
  static get tableName() {
    return 'user';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['name'],

      properties: {
        id: { type: 'integer' },
        name: { type: 'string', minLength: 1, maxLength: 255 },
        status: {type: boolean},
      },
    };
  }
}

I'm trying to write this query:

Select palce.name user.name
from palce, user
where name like '%string%' and user.status is true and place.status is true

So I tried to convert the SQL query into Objection.js query to run my program:

Place
.query()
.joinRelation('user')
.where(Place.raw('lower("name")'), 'like', '%string')
.andWhere('user.status', true)
.andWhere('place.status', true)
.then(result => {
console.log(results);
});

I tried to test the program with postman, howver I got this error:

{
    "name": "error",
    "length": 119,
    "severity": "ERROR",
    "code": "42703",
    "position": "146",
    "file": "parse_relation.c",
    "line": "3183",
    "routine": "errorMissingColumn"
}

The problem occurs when I try to make the name of a place in lowercase:

Place.raw('lower("name")')

So, how can I combine databases to get the data that I need? I'm using postgre such a database.

Answer 1

Your code above did have some syntax errors etc. but after fixing them (https://runkit.com/embed/5b5bu44kr79m) objection seems to generate following SQL, for the query:

select "place".* 
  from "place" 
  inner join "user" as "user" 
    on "user"."id" = "place"."user_id" 
  where
    lower("name") like ? and 
    "user"."status" = ? and 
    "place"."status" = ?

Maybe you have typo in your column name or something like that (since error is coming from DB that it cannot find a column with certain name)?