Objection.js: Fields in .select() clause repeated in JoinEagerAlgorithm

[jeff-kilbride]

I've found something interesting using the JoinEagerAlgorithm. I have a Video model with the following:

    static get defaultEagerOptions() {
        return {
            aliases: {
                'bucket': 'b'
            }
        };
    }

    static get relationMappings() {
        return {
            bucket: {
                relation: Model.BelongsToOneRelation,
                modelClass: 'Bucket',
                join: {
                    from: 'videos.bucket_id',
                    to: 'buckets.id'
                }
            }
        };
    }

I ran this query:

Video.query()
  .findById(1518)
  .select('videos.id', 'videos.price', 'videos.created', 'videos.updated')
  .eager('bucket')
  .modifyEager('bucket', builder => builder.select('name'))
  .debug()
  .then(console.log);

And I got this generated SQL:

select `videos`.`id`, 
  `videos`.`price`, 
  `videos`.`created`, 
  `videos`.`updated`, 
  `videos`.`id` as `id`, 
  `videos`.`created` as `created`, 
  `videos`.`updated` as `updated`, 
  `videos`.`price` as `price`, 
  `b`.`id` as `b:id`, 
  `b`.`name` as `b:name` 
from `videos` as `videos` 
  left join (select `name`, `id` from `buckets`) as `b` 
    on `b`.`id` = `videos`.`bucket_id` 
where `videos`.`id` = ?

Notice the fields from my .select() method are repeated with the as clause added. However, the data returned was correct:

Video {
  id: 1518,
  price: 9.99,
  created: 2016-06-30T12:53:22.000Z,
  updated: 2016-12-01T12:28:08.000Z,
  bucket: Bucket { name: 'vcs-9-1' } 
}

Out of curiousity, I tried the following:

Video.query()
  .findById(1518)
  .select(
    'videos.id as id', 
    'videos.price as price', 
    'videos.created as created', 
    'videos.updated as updated'
  )
  .eager('bucket')
  .modifyEager('bucket', builder => builder.select('name'))
  .debug()
  .then(console.log);

This time, only the videos.id field was repeated in the generated SQL:

select `videos`.`id` as `id`, 
  `videos`.`price` as `price`, 
  `videos`.`created` as `created`, 
  `videos`.`updated` as `updated`, 
  `videos`.`id` as `id`, 
  `b`.`id` as `b:id`, 
  `b`.`name` as `b:name` 
from `videos` as `videos` 
  left join (select `name`, `id` from `buckets`) as `b` 
    on `b`.`id` = `videos`.`bucket_id` 
where `videos`.`id` = ?

But the videos.id field was missing from the returned data:

Video {
  price: 9.99,
  created: 2016-06-30T12:53:22.000Z,
  updated: 2016-12-01T12:28:08.000Z,
  bucket: Bucket { name: 'vcs-9-1' } 
}

Let me know if there is any more info I can provide that would help. Thanks!

[elhigu]

Thanks for the report I have no idea why that happens, lets wait for @koskimas to come back if he knows it from top of his head 👍

[koskimas]

Don't know why this happens, but I don't think the extra selects do anything at all. The database engines surely ignore identical extra selects. I'll fix this at some point, but unless this causes some problems to you, It won't be a top priority.

[jeff-kilbride]

@koskimas No, it's not a big problem. I was just surprised to see it and wanted to bring it to your attention. The only issue that could potentially bite people is the last example, where the id field is in the select statement, but not returned in the result set. However, I don't use it this way. I was just experimenting to see what I would get.

Hope you had a nice holiday, by the way! :)

[koskimas]

Thank you for bringing this up! This something we need to fix for sure.

Thanks, my "holiday" was interesting. I sailed across the atlantic ocean on a tiny sail boat with 6 strangers 😀

[jeff-kilbride]

That sounds great! ⛵️ 😄

I spent 6 years in the U.S. Navy after university, mostly in the south pacific, so I have a fondness for the ocean. The atlantic can be very rough, even in a large ship!