BelongsToMany always returns empty array


#1

Hello everyone
I am having some problems with belongsToMany relationship in adonisjs. The problem is that when I run the query I get an empty result for the relationship.
this is my model

class Sheet extends Model {
    static get visible () {
        return [
            'id', 'package_id', 'instructor_id', 'created_by'
        ]
    }

    /* Relationships */

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

    package () {
        return this.belongsTo('App/Models/Package')
    }
}

and this is the query I am running:

await Sheet.query()
           .with('users')
           .with('package')
           .fetch()

The package relationship works perfect, but the users one it always return an empty array with no other errors or something. I tried to force the pivot table, the foreign keys and so on and always I get an empty result.
I cannot figure what I am doing wrong.


#2

Can you please debug the SQL queries and share the output of them?


#3

don’t know how to debug the sql query
I tried to use to toString() and toSQL() methods, but all they return is just a simple select, something like this select * from sheets


#4

You need to set DEBUG env, like so:

DEBUG=knex:* node app.js

#5

Also feel free to refer the documentation. It’s here https://adonisjs.com/docs/4.1/database#_debugging


#6

Ok, I’ve set the db debug to true and now I can see the entire query on my console. the query looks like this:

select "users".*, "sheet_user"."user_id" as "pivot_user_id", "sheet_user"."sheet_id" as "pivot_sheet_id" 
from "users" 
inner join "sheet_user" on "users"."id" = "sheet_user"."user_id" 
where "sheet_user"."sheet_id" in (?)

it seems to be correct, but somehow I get an empty array in return
the bindings are also correct


#7

Mind sharing a repo using which I can reproduce the issue?


#8

Ok, I’ve setup this repo with my code: https://gitlab.com/melokki/backend


#9

Up, any ideas?


#10

I can see in the debug console that some of the bindings might not be correct

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '1' ],
  __knexQueryUid: '174912b0-c921-4e45-a69d-3629b4ee388a',
  sql: 'select "users".*, "sheet_user"."user_id" as "pivot_user_id", "sheet_user"."sheet_id" as "pivot_sheet_id" from "users" inner join "sheet_user" on "users"."id" = "sheet_user"."user_id" where "sheet_user"."sheet_id" in (?)' }

here 1 is a string '1' instead of integer. might be this the problem?


#11

Sorry for getting late on same.

The reason I was not able to check your repo, it asks me to login to Gitlab.

Again, it’s hard to tell what exactly is going on. The number 1 should be an integer, if you set it up as integer inside your database.


#12

Well, I got no answer since I posted the repo so I’ve deleted the repo. That’s why it asks for login.
I tried with a fresh installation and the same thing happened.
sheet_id and user_id are set as bigInteger into sheet_user table and id is set as bigIncrements in sheets table.


#13

Yes, so string doesn’t make a difference, since Javascript driver casts bigInteger to string, so it should work fine

Can you put the repo back, I’ll check it now. Also please drop required migrations and seeds for dummy data


#14

Ok, I’ve managed to fix the problem.
I am stupid, in the sheet table migration the id was increments instead of bigIncrements… my god.
I just double checked again after you said to me to set the field as integer inside database and changed it to bigIncrements and now it’s working.
here is my output:

{
    "status": "success",
    "data": [
        {
            "id": "1",
            "package_id": 1,
            "instructor_id": "1",
            "name": "sheet name",
            "wedding_date": "2018-04-23T00:00:00.000Z",
            "wedding_song": null,
            "created_by": "1",
            "package": {
                "id": 1,
                "name": "test 1",
                "description": "test 1",
                "price": 4000,
                "appointments": 5,
                "created_at": "2018-04-23 08:46:39",
                "updated_at": "2018-04-23 08:46:40"
            },
            "users": [
                {
                    "id": 1,
                    "first_name": "Clayton",
                    "last_name": "Webb",
                    "email": "we@magak.bf",
                    "phone": "(968) 930-9101",
                    "pivot": {
                        "user_id": "1",
                        "sheet_id": "1"
                    }
                },
                {
                    "id": 2,
                    "first_name": "Joseph",
                    "last_name": "Daniel",
                    "email": "cihtet@usilarun.de",
                    "phone": "(721) 960-1479",
                    "pivot": {
                        "user_id": "2",
                        "sheet_id": "1"
                    }
                }
            ]
        }
    ]
}

thank you @virk :slight_smile: