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.

Mikael Lepistö · Accepted Answer · 2017-10-23 08:06:45Z

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)?

answered Oct 23, 2017 at 8:06

Mikael Lepistö

19.9k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

P.A · Accepted Answer · 2017-10-23 08:14:26Z

1

You have to write Place.raw('lower("name")') without quotes like this :

Place.raw('lower(name)')

answered Oct 23, 2017 at 8:14

P.A

8216 silver badges17 bronze badges

1 Comment

Mikael Lepistö Over a year ago

That shouldn't make any difference in this case. lower("name") is perfectly correct postgresql syntax. From question it is just not possible to know, which of the column names is wrong, because the question didn't include schema.

Collectives™ on Stack Overflow

How parse data using join on Objection.js

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related