1

How can I get data from two joined tables? Suppose, there are two models called Category (CategoryId, CategoryName) and Product(ProductId, ProductName, CategoryId), Is there a way to get a result like:(ProductId, ProductName, CategoryId, CategoryName)

Anoop Thiruonam
  • 2,567
  • 2
  • 28
  • 48

1 Answers1

3

There should be a relation between your Category model and your Product model. A Category hasMany Products and each Product belongsTo a Category. So your model json files should be something like

Category:

{
  "name":"Category",
  "properties":{
    "CategoryId": {
      "type":"Number",
      "id":1
    },
    "CategoryName":{
      "type":"String"
    }
  },
  "relations": {
    "products": {
      "type":"hasMany",
      "model":"Product",
      "foreignKey":"CategoryId"
    }
  }
}

Product

{
  "name":"Product",
  "properties":{
    "ProductId: {
      "type":"Number",
      "id":1
    },
    "ProductName":{
      "type":"String"
    },
    "CategoryId": {
      "type":"Number"
    }
  },
  "relations": {
    "category": {
      "type":"belongsTo",
      "model":"Category",
      "foreignKey":"CategoryId"
    }
  }
}

Of course these json definitions must be completed with the corresponding options and datasource properties, which only you do know.

The relation between those two models will add endpoints to loopback explorer so you can query for products in a certan category:

GET /api/Categorys/:id_category/products

of the category to which a product belongs

GET /api/Products/:id_product/category

Please note that, unless you specify a plural option for Category, its plural will be Categorys. This is not a typo.

Finally, if you want to query for a product and its category, you would use the include filter

GET /api/Products/:id_product?filter[include]=category

hope it helps.

ffflabs
  • 17,166
  • 5
  • 51
  • 77
  • Thank you for your answer. So this also means that if you want joined data, a relationship is must. – Anoop Thiruonam Jun 06 '15 at 06:36
  • Kind of. You could declare a custom remote method for Product which, for each result, made a call to Category.find and extended the product properties with its Category. But this relation substitute would be inneficient and probably overkill for common use cases. – ffflabs Jun 06 '15 at 18:57
  • Okay, Thank you for your response! – Anoop Thiruonam Jun 07 '15 at 07:33
  • This common loopback model technique unfortunately doesn't work when you have multiple foreign keys!!! – kensai Oct 25 '16 at 15:49
  • @kensai how so? You mean entities that belong to more than one entity? I have that use case and it works. Perhaps I can help you track down what's stopping yours from working – ffflabs Nov 03 '16 at 19:35
  • [loopback issue](https://github.com/strongloop/loopback/issues/2080) is describing the issue, you can't have "foreignKey":["id1","id2"] definition in place in the moment. The only technique I know about how to override it is to use custom SQL statements. But welcome any model-driven solution. – kensai Nov 09 '16 at 09:34
  • Oh, I see. I thought you meant entites that are just bridges between other two, so they belongTo both, and these haveMany of each other Through the bridge entity. So yeah, the bridge entity can have the tuple id1 id2 as PK, but cannot have children entities – ffflabs Feb 13 '19 at 19:27