Clash Royale CLAN TAG

Querying database using 'with' and not getting the expected outcome

So I have two tables that share a many to many relationship:

1) Games, with info from matches created by users and

Games

2) Users.

Users

Apart from the user/games relationship where a game belongs to many users and a user belongs to many games, there's also a relationship where a user can APPLY for many games and a game can BE APPLIED for by many users. For this relationship I created a junction table called requests.

game

users

user

games

user

games

game

users

requests

I'm using AdonisJs, so I set the model Games with

Games

applicants () {
return this.belongsToMany(
'App/Models/User',
'game_id',
'applicant_id'
).pivotTable('requests')
}


and the model Users with

Users

applications () {
return this.belongsToMany(
'App/Models/Game',
'applicant_id',
'game_id'
).pivotTable('requests')
}


I need to query for all the users that applied to any of the authenticated user's games (defined by having said user's id as user_id)

user_id

When I use the code

async getAllUserGames ({ auth, response }) {
const allUserGames = await Game.query()
.where('user_id', auth.current.user.id)
.with('applicants')
.firstOrFail()
return response.json({
status: 'success',
data: allUserGames
})
}


it works, but as I used .firstOrFail() I only get the info from the first game that has an application.

.firstOrFail()

If I get rid of this line it doesn't return any of the applicants, only the user's games, as if I didn't have the line .with('applicants') also.

.with('applicants')

I'm still trying to get my grip around querying databases, so I would appreciate any insight as to why this is happening.

1 Answer
1

Did you find a solution for your problem ?
Your code is correct, just replace the

.firstOrFail()


By

.fetch()


Or

.findOrFail()


Since firstOrFail will only return the first element matching your query

Usually when it comes to use relations with adonisjs, you need to call the method that will tell lucid to fetch the elements matching your query.

Once your retrieve allUserGames, you will need to update your query so that it will match exactly what you need.

firstOrFail returns a single element whereas findOrFail and fetch will return a pagination object

const { rows: realUserGames } = allUserGames


The complete example would be

async getAllUserGames ({ auth, response }) {
const allUserGames = await Game.query()
.where('user_id', auth.current.user.id)
.with('applicants')
//.findOrFail()
.fetch()

const { rows: realUserGames } = allUserGames

return response.json({
status: 'success',
data: realUserGames
})
}


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.