Loopback: How to do join and sum for relational tables

[nmklong]

So for example i have these 2 tables:

A {
 id_a,
 value
}

B {
 id_b,
 id_a,
 value_to_be_summed
}

A has many B's

I want to do a search for value of A, but i also want to join and sum value_to_be_summed for B's that have the same id_a

I searched but not seeing anywhere in the documents to guide that, please help, thank you

[Amir-61]

ummmm... I believe you know you can query related models using foreign keys (please see here) However your problem seems different since you want to query by an aggregate function like group by and sum in SQL and sum them up... Unfortunately I don't think if we have support for that... I believe the alternative you can consider is executing native SQL (please see here)... Also could you please let us know what connector you use?

@superkhau @bajtos Is there any other workaround which I'm not aware of to achieve what @nmklong is trying to do?

Thanks :-)

[bajtos]

I think executing native SQL is the best solution here, especially when considering performance.

[Amir-61]

Thanks Miroslav!

Hey @nmklong,

I will close this ticket soon since your question is answered...

Thanks :-)

[superkhau]

I think executing native SQL is the best solution here, especially when considering performance.

I think we should consider creating a query generation engine for connectors. Instead of writing it all out by hand inline. Like a separate module that is in lib for each connector that is only responsible for generating queries. This will allow us to build up queries on the fly like active record for example instead of having one giant object with nested items.

find({id: 1})
  .where({name: 'bob'})
  .join({customers: 'id')
  .execute(cb);

Not sure if this second part is a good idea or bad idea though.

[nmklong]

Thank you guys, i think i'll go with raw sql to get the ids and then use the ORM to give the response back