0

Let's say I have two tables :

People:

 name : place
----- : ------
ben   : park
phil  : office

Food:

food_place  : vegetable
----------- : ---------
ben@park    : carrots
phil@office : potatoes

I'd like to create a Relation Mapping, I tried:

  static get relationMappings() {
    const knex = People.knex();
    return {
      food: {
        relation: Model.BelongsToOneRelation,
        modelClass: `${__dirname}/Food`,
        join: {
          from: knex.raw('CONCAT(people.name, "@", people.place)'),
          to: 'food.food_place',
        },
      },
    };
  }

That didn't work because objection expected the from property to match column name (Error: People.relationMappings.food: join.from must have format TableName.columnName).

The idea was to join tables like:

INNER JOIN Food ON Food.food_place = CONCAT(people.name, "@", people.place)

QUESTION : Is there a was i can do this with relation mappings, or should i use modifiers instead ?

Hollyol
  • 827
  • 1
  • 13
  • 25

1 Answers1

0

"@" in double quotes will cause error. Use single quotes instead '@'. The resultant query should look like this :

select * from people as people inner JOIN Food ON Food.food_place = CONCAT(people.name, '@', people.place)

Replace like this

join: {
          from: knex.raw("CONCAT(people.name, '@', people.place)"),
          to: 'food.food_place',
        },
Bensu Rachel
  • 184
  • 1
  • 5