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:
(for all information regarding each screen type),
(for all information regarding each user. The users schema should contain a column for company_id),
(for all information regarding each company),
(a pivot table holding information about users and their screens), and
(a pivot table holding information about companies and their screens).
In your model files, assign appropriate relationships:
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()
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.