Nodejs Libraries - Mjoin collision when both tables have an "id" attribute?

icdeb

I'd like to show a count of associated (one-to-many) records within in the datatable.

The database would look like:

USER TABLE
id name
1  bob
2  jane
3  mary

PETS TABLE
id name owner
1  dash 3
2  jet  1
3  spud 3

Here mary owns both dash and jet and bob owns jet.

I'd like to display a datatable such as:

OWNER PETS
bob   1
jane  0
mary  2

I found this which is basically the same thing: https://datatables.net/forums/discussion/36112/summany-data-count-sum-average-etc

There is an example for this with the nodejs libraries: https://editor.datatables.net/manual/nodejs/mjoin

// Initialise the editor instance
let out = new Editor(db, 'user')
.fields(
  // Auto incremented id
  new Field('user.id').set(false),
  // Required field
  new Field('user.name').validator(Validate.notEmpty(new Validate.Options({ message: 'Name is required' })))
)
// Total the associated pets
.join(
  new Mjoin('pet')
    .link('user.id', 'pet.owner')
    .fields(new Field('id'))
)
..

With this I get the following:

code: 'ER_NON_UNIQ_ERROR',
errno: 1052,
sqlMessage: 'Column \'id\' in on clause is ambiguous',
sqlState: '23000',
index: 0,
sql: 'select distinct `user`.`id` as `dteditor_pkey`, `pet`.`id` as `id` from `user` inner join `pet` on `id` = `user` where `user`.`id` in (\'1\', \'2\', \'3\')'

So I tried:

...
.join(
  new Mjoin('pet')
    .link('user.id', 'pet.owner')
    .fields(new Field('pet.id as petId'))
)
...

And I get the same error but the query is slightly different:

sql: 'select distinct `user`.`id` as `dteditor_pkey`, `pet`.`id` as `petId` from `user` inner join `pet` on `id` = `owner` where `user`.`id` in (\'1\', \'2\', \'3\')'

Seems that the issue is actually the part where it has:

"inner join `pet` on `id` = `owner`"

That should be:

"inner join `pet` on `user`.`id` = `pet`.`owner`"

So I thought okay at this point then I should just be able to use the aliases?

...
.join(
  new Mjoin('pet')
    .link('dteditor_pkey', 'pet.owner')
    .fields(new Field('pet.id as petId'))
)
...

But then I get an error saying:

"Error: Mjoin fields must contain both the table name and the column name"

I noted what seems to be the same bit of confusion over in the .net libraries: https://datatables.net/forums/discussion/38165/editor-mjoin-error-join-was-performed-on-the-field-x-which-was-not-included

Is there a way to do this without the use of identifiers in each table like user_id and pet_id? I'd prefer not to use those or a joining table and rather stick to this standard one-to-many relationship model.

It feels like a bug but maybe this part of things is lacking a bit of documentation.

I've been battling with this for quite a while now and at this point it seems like the only way to accomplish this would be to monkey patch the response output?

Does anyone have any suggestion or feedback on how to accomplish this using the native Datatables/Editor functionality?

Many thanks!

allan

.fields(new Field('pet.id'))

I think should do it?

Allan

icdeb

Hi Allan

Thanks for taking the time to respond.

When I try the following:

...
.join(
  new Mjoin('pet')
    .link('user.id', 'pet.owner')
    .fields(new Field('pet.id'))
)
...

I get:

code: 'ER_NON_UNIQ_ERROR',
errno: 1052,
sqlMessage: 'Column \'id\' in on clause is ambiguous',
sqlState: '23000',
index: 0,
sql: 'select distinct `user`.`id` as `dteditor_pkey`, `pet`.`id` from `user` inner join `pet` on `id` = `owner` where `user`.`id` in (\'1\', \'2\', \'3\')'

This is with:

"datatables.net-editor-server": "^1.9.0",

In SQL if I run the query:

select distinct `user`.`id` as `dteditor_pkey`, `pet`.`id`
from `user`
inner join `pet` on `user`.`id` = `pet`.`owner`
where `user`.`id` in ('1', '2', '3')

Then it gives the desired result so it seems like the real issue here is that Mjoin is generating a query that does not contain the prefix of the table name for the inner join part.

It is as above where:

inner join `pet` on `id` = `owner`

Should actually be:

inner join `pet` on `user`.`id` = `pet`.`owner`

But it doesn't seem like there is a way for me to generate this query using the Mjoin / fields methods and hence me reasoning that this might actually be a bug?

icdeb

Thinking about it again it actually doesn't really add up,

If you try to call the .link() method in Mjoin without the table name such as:

.link('id','owner')

Then you get the error:

join fields must contain both the table name and the column name

So table names are explicitly required as inputs

.link('user.id','pet.owner')

But then you get the ambiguous error containing the inner join clause where they are not used

inner join `pet` on `id` = `owner`

That's some confusing behavior and is most likely unintended?

allan

Yes, something a bit odd going on here. I'll investigate a report back.

Allan

allan

Is it an option to introduce a join table (to link between the user and pet tables)? I'm feeling nervous about your setup above because of how Editor's libraries handle a edit for an mjoin. They will delete the rows and then readd them. But that means meta information is lost (the pet name for example).

If you can, I'd very much suggest using a join (/junction) table.

Allan

icdeb

Hi Allan

Thanks for the response. I read that part in the documentation but didn't click until you said that now.

It would actually case an issue for my use case, yes. I still think that it needs a look at thought because link isn't using the table names that we are forced to define? I imagine that would cause issues in other cases too.

In any case, I have opted to patch the response output instead of implementing a join table.

As it is just one query either way so I don't think it would create much of a performance difference and as a plus I was able to total the associated pets on the server side so we're not sending a larger response of taxing the client to do so on its end.

If anyone else in the future is wondering, my work around looks something like this:

let out = new Editor(db, 'organisation')
...

// Get the user ids
const userIds = out.data.map(record => record = record.user.id)
// Get the relative pet information from the database
const pets = await Pet.find({
  where: {
    owner: userIds
  },
  select: ['owner']
})
// Total the pets for each owner
for (let i = 0, n = out.data.length; i < n; i++) {
  const record = out.data[i]
  record.user.pets = pets.filter(pet => pet.owner === record.user.id).length
}
// Send the response output
return res.json(out)

allan

That's a nice workaround - thanks!

There is something odd in the libraries there and I filed a bug for it to be investigated (sorry I should have said that). I'm not immediately certain what is going on there I'm afraid, but it is worth checking out.

Until then, your workaround looks good.

Allan