Load related models and withCount together

Hello again. Thank you so much for taking the time to respond to me.

I have some questions because I want to check the count or the length of the item that a model has.

First of all, I’ll describe with an example of what I want to achieve and the stuff I have done to do it that unfortunately occurs that is not giving me the result I want.

I have many models related to each other as if it was a cascade.

So I have ModelA, ModelB, ModelC and ModelD. ModelB belongs to ModelA, ModelC belongs to ModelB and so on.

So we have a hierarchical relationship like this:

ModelA -> ModelB -> ModelC -> ModelD.

So what I want to do is to check how many of each model a parent model has. For example, I want to know how much ModelBs each ModelA contains, how much ModelCs each ModelB contains and so on.

To do this, I have tried two different approaches. I’m going the code of the approach and the result or error message I get.

//This code goes inside a method from a controller, so it uses params.id in the where function.

const modelA = await ModelA
                    .query()
                    .where('id', '=', params.id)
                    .withCount('modelBs', (builder) => {
                        builder.withCount('modelCs', (builder2) => {
                              builder2.withCount('modelDs')
                           })
                        })
                     .fetch()

So as I mentioned before, I want to get the number of items (submodels) a model has. With this approach I get the following error:

ER_OPERAND_COLUMNS: Operand should contain 1 column(s)

The other approach I have tried is the following.

const modelA = await ModelA.find(params.id)
await modelA.load('modelBs.modelCs.modelDs')

const modelBs = modelA.getRelated('modelBs')
        
if(modelBs.length > 0) {
    modelA.complete = true;
} else {
    modelA.complete = false;
}

return modelA

//When I check what modelA contains, it contains the completed field as false ALWAYS. So it means that modelBs.length is not working.

With this approach I made use of the function getRelated to get the array of the related model. In this example I get all the modelBs of modelA. However, it seems like the segment of code modelBs.length does not return anything. So there’s no “real way” of knowing if the length of the arrays is longer than 0.

And now, this is a related issue with this. In the previous segment of code. If I return the modelBs.length in my controller liket this: return modelBs.length , I receive a 204 response, which in fact contains nothing. But If I only return modelBs like this: return modelBs , it returns the whole array with its data. Why does this happen and why the .length function seems like no working in this case.

Thank you so much for taking your time to read me again.
I want to solve my main issue that is getting the count or the length of each submodel’s model. And if it’s possible to get the answer for the last issue I mentioned It’ll be great.

EDIT: I have to mention that I already checked my database and It actually contains several data in the tables of the mentioned models.

1 Like

You can turn on debugging and share the query generated for this code?

Sure, this is the result I get:

info: serving app on http://127.0.0.1:3333
  knex:query select * from `model_a` where `id` = ? limit ? undefined +0ms
  knex:query select * from `model_b` where `model_a_id` = ? undefined +26ms
  knex:query select * from `model_c` where `model_c`.`model_b_id` in (?, ?, ?) undefined +11ms
  knex:query select * from `model_d` where `model_d`.`model_c_id` in (?, ?, ?, ?) undefined +4ms

To support my explanation of above. This is the complete error code I get when I reach the route “show” of the modelA with the id = 2.

route: api/v1/model-a/:id

Error 500
select *, (select *, (select *, (select count(*) from `model_d` where `model_c`.`id` = `model_d`.`model_c_id`) as `model_d_count`, count(*) from `model_c` where `model_b`.`id` = `model_c`.`model_b_id`) as `model_c_count`, count(*) from `model_b` where `model_a`.`id` = `model_b`.`model_a_id`) as `model_b_count` from `model_a` where `id` = '2' - ER_OPERAND_COLUMNS: Operand should contain 1 column(s)