Select statements with relations


#1

I have a Post model and a Tag model, each with a belongs to many relationship with the other(many to many).

var posts = await Post
  .query()
  .with('tags')
  .fetch()

Works great. As expected I get something like:

[{
  "id" : "23",
  "title" : "Test Post 23",
  "body" : (Too long to post in forum, but all ok),
  "tags" : [{ "id" : 2 , "name" : "javascript", "__meta__" : (pivot table info)}, 
            { "id" : 3 , "name" : "node" , "__meta__" : (pivot table info)}, 
            ...
            ...
}, 
(more good output)
]

But chaining a .select() statement (before or after the .with(‘tags’) call) seems to kill the eager relation fetch. So:

var posts = await Post
  .query()
  .with('tags')
  .select('id', 'title')
  .fetch()

Gives empty “tags”, returning:

[{
  "id" : "23",
  "title" : "Test Post 23",
  "tags" : []
 }, 
(more output)
] 

Is this expected behavior? Is there a doc anywhere I’m not seeing?

I’m currently just leaving off the select statement and doing a manual field ‘select’ with a .map function. It works, but I’d love to only pull the data I need and not have to mess with the extra function call. Any thoughts greatly appreciated.


#2

Trying to add ‘tags’ to the Post .select() statement also fails. (As it should, since there is no “tag(s)” column). Adding a callback to the .with() call doesn’t change anything either. Eg.

var posts = await Post
  .query()
  .with('tags', (builder) => {
    builder.select('id', 'name')
  })
  .select('id', 'title')
  .fetch()

Still gives an empty ‘tags’ property.

Slightly off topic-- Interestingly, without the main (‘Post’) .select() call, the relation callback (‘tags’) .select() works to limit tag fields returned, but won’t omit the “__meta __” pivot table info, even if only one column (eg. ‘name’) is requested. This isn’t really a problem for my app, just an observation while troubleshooting.


#3

Before I setup a project and try to reproduce it, would you mind trying the following way.

var posts = await Post
  .query()
  .with('tags')
  .select('posts.id', 'posts.title')
  .fetch()

#4

Thanks @virk for taking the time to respond.

.select('posts.id', 'posts.title') 

gives the expected columns from posts, but still leaves the ‘tags’ relation empty. I’ve also tried several other variations on this scheme. I still can’t get the ‘tags’ relation to load if there is a .select() call on Post .

I’ve set up a github repo to easily reproduce this behavior.

Questions, comments, suggestions more than welcome.


#5

Ahh, you are missing the id from the posts, Lucid doesn’t know how to build relations, since Id is missing.

    const dotselect = await Post
      .query()
      .with('tags')
      .select('posts.id', 'posts.title', 'posts.body')
      .limit(limit)
      .fetch()

#6

@virk I cannot write ‘thank you’ enough in this post. Such a simple solution (obvious even, in retrospect), but I’d have never figured it out. You, sir, are the man.