Sparse fieldsets are not reflected in SELECT query

[chojnicki]

Hi. I'm considering using json:api in next API project, but I have serious performance concerns.

Let's say I want to get titles of articles in DB. Thanks for sparse fields response will be light, because it will not contain long article text and other not needed attributes.

http://localhost/api/v1/articles?fields[articles]=title

But in mysql log I can see:

Execute	select * from `articles`

Why?

It should be

Execute	select id, title from `articles`

Is this normal? This package does not prepare query with select's that are actually needed, instead just always fetching full rows? I cannot find other issues about this or in docs. Can this be handled?

Currently it will affect server performance, because this query can take 10MB instead 10KB...

Regards :)

[lindyhopchris]

Hi! Yes, so this is very high-up on my to-do list. The reason I haven't implemented it yet is all the apps I use this package for can handle just doing SELECT * - i.e. it would be a performance improvement but the performance issue isn't huge on the apps I use this package for, so it hasn't been a high priority yet.

Saying that, your issue highlight that I should probably get this in sooner rather than later.

One thing worth pointing out is that this isn't as straightforward as your description makes it seem. It involves some analysis of the include paths to determine if there are any has-one relationships where the key is stored on the inverse model of the relationship.

I will also need to change all the eager loading to factor in any fields that have been specified by the client. And if that's a deep path, e.g. posts.comments.user then I need to change the eager loading at each path becasue different fields could have been specified for posts, comments and/or users.

So as you can hopefully see, none of this is straightforward and there's quite a bit to work through to get this working.

The other thing that needs to be decided is what to do if the client hasn't specified any fields for a specific resource type... should I just do a SELECT * or should I select all the columns that the resource has fields for?? Would be interested to hear your thoughts on that.

[chojnicki]

Sure relations are always tricky with manual SELECT, I understand that it's not one-line fix but more tricky one. Even lighthouse (graphql) did not figured this out if I remember correctly.

About priorities - I'm glad that you already considered this and it's high on TODO. But even that it's my issue, in my opinion much more important than this would be generating openapi spec #19 , because I saw that many people here tried and failed ;) We can buy better server to overcome SELECT * performance, but we cannot buy more time for ourselves for writing manually api docs, that for JSON:API are horrific complicated and long.

Anyway thank's for your work. It makes old good REST much more useable.

[freestyledork]

Any progress or updates on this feature?

[lindyhopchris]

Yeah this is towards the top of my list of priorities now. As in, the next major release of this package will have it as a feature.

I don't have any Open Source time at the moment - that's the limiting factor. I'm hoping to get some from May onwards.

[dimitvassilev]

Hi and thanks for keeping optimizing this amazing package! For what it's worth, on the topic of what to do if no sparse fields are specified, it seems to me that taking the fields present in the resource should be the appropriate approach, as the client is only interested in the resource and there might be thinner and fatter resources over one and the same model which could be very big and its underlying table even bigger (as a model with a custom query builder or global scope applied, which reduces selected columns). Sorry if I'm making an obvious point, but there is also something else I realised, which further complicates how sparse fieldsets would translate to the select statement - since the resource attribute names might not follow the name-mapping convention to corresponding column names and, what is more, they could be calculated in some custom way, e.g. combining first and last name from db in a single attribute 'name' in the resource, probably there should be some mechanism to register custom rules as to what column names to include in the sql statement in case a field has been requested (and it should allow for one-to-many mapping). With this figured out for the cases when sparse fieldsets are included in the request, I guess it should be straightforward, though, to only include what is needed for the fields in the resource rather than 'select *'. I'd be happy to help if you think an outside person could work it out with some guiding points about what approach you have in mind.

[lindyhopchris]

Thanks for the offer of help. Yeah, I need to work through all these problems when I start looking at how to implement the feature. An added complication is I also need to specify all the columns when loading models via eager loading. So there's quite a few things to work out.