I'm struggling to query the Pivot table

so I’ve made a pivot table that holds id, user_id and listing_id.
it’s relationship, from the User model:

ListingUsers() {
    return (
        this.belongsToMany('App/Models/Listing')
            .pivotModel('App/Models/ListingUser')
            .withPivot(['id'])
    )
}

the query is

return await auth.user.ListingUsers().fetch()

what i want is to just query the pivot table and match on both foreign keys it contains. Currently it’s inner joining the listings table with the pivot table, which is bringing back the entire listing row along with the pivot table.

can someone point us in the right direction of how to just query and return a row from the pivot table?

Hey @Mr.M! :wave:

I didn’t really understand what you want to do.
Could you please write a RAW SQL query or provide the final object you are expecting to have?

@Mr.M Hi there:

Assuming this is a many-to-many relation:

Listing columns (id, listing_name)
Users columns (id, user_name)
ListingUsers columns (id, user_id, listing_id)

Try this:

const the_query = `
  SELECT  
    users.id as user_id,
    users.user_name,
    listing.id as listing_id 
    listing.listing_name
FROM listing_users
INNER JOIN
users ON listing_users.user_id =  users.id
INNER JOIN
listings ON listing_users.listing_id = listings.id
WHERE
....

const results = (await Database.schema.raw(the_query)).rows;

also check this link:
https://adonisjs.com/docs/4.1/relationships#_database_tables_4
in that link ‘UserCar’ is exactly what you are looking for a many-to-many relation

Regards

Hey,
thanks for the answers and apologises for the late reply.
i did find a way around the problem by using the database class (knex) stuff, though i’m still interested in how you could achieve the same with with Lucid, since thats what adonis seems to favour.

this is the work around which is returning the correct results.

let res = await Database.table('listing_users')
            .where('user_id', auth.user.id)
            .where('listing_id', ListingID)

postgres query

select * from listing_users
where user_id  = $1and listing_id = $2

this will just return me the contents of the listing_users table, where as the
the lucid query seems to be doing some inner joins (as @pikitgb 's query seems to suggest) which is returning the listing table row (which i don’t want) as well as the listing_users row within it, (the bit i do want).

I don’t really understand what you are trying to get from the table or which table, maybe if you explain the problem I can help with but that said I’m Not sure how to do it with Lucid, i use SQL when i can (for me is easier to understand pure SQL than a lot of Lucid joins)

Regards.