Syntax error at or near "$1" when i try to use a UNION in queryRaw

I’m trying to make a query using queryRaw() but i’m receiving:

## select "screens".* from "screens" where id IN $1 UNION ALL $2 - syntax error at or near "$1"

What i tried:

const screensCompanyUser = await Database

      .query()

      .table('screens')

      .select('screens.*')

      .whereRaw('id IN ? UNION ALL ?', [screenUsers, screensCompany])
1 Like

Hello @ veronesecoms.

Firstly, you don’t need to bring in the Database class. Secondly, your join statement is not correct.
A good application begins with a well-structure database. I am assuming that your database schemas are properly defined and appropriate relationship existing in the model files.

In your question, I see 5 schemas which should be defined:

  1. screens: (for all information regarding each screen type),
  2. users: (for all information regarding each user. The users schema should contain a column for company_id),
  3. companies: (for all information regarding each company),
  4. screen_user: (a pivot table holding information about users and their screens), and
  5. screen_company: (a pivot table holding information about companies and their screens).

In your model files, assign appropriate relationships:

User.js

company() {
  return this.belongsTo('App/Models/Company')
}
screens() {
  return this.belongsToMany('App/Models/Screen')
}

Screen.js

users() {
  return this.belongsToMany('App/Models/User')
}
company() {
  return this.belongsToMany('App/Models/Company')
}

Company.js

users() {
  return this.hasMany('App/Models/User')
}
screens() {
  return this.belongsToMany('App/Models/Screen')
}

So if you structure your database as listed above and define the above relationships in your models, try the code snippets below:

const Screens = use('App/Models/Screen')
const User = use('App/Models/User)
const Company = use('App/Models/Company)
...
/* 
 * Snippet 1:
 * To get the screens used by a particular user. 
 * Remember that the user is already assigned to company.
 */
const user_id = 8
const user = User.find(user_id)
const screens_user = await user.screens().fetch()

/* 
 * Snippet 2:
 * To get the screens used by a particular company. 
 */
const company_id = 7
const company = await Company.find(company_id)
const screens_company = await company.screens().fetch()

/* 
 * Snippet 3:
 * To get screens, with their users and companies, use eager loading. 
 */
const screens_users_companies  = await Screen.query()
      .with('users')
      .with('companies')
      .fetch()

Due to the many-to-many relationship between Screen and User/Company, I think joining the tables won’t be very feasible. I stand to be corrected. Cheers.

2 Likes