The loopback relational database has a multi-valued pivot table

Question

I seem to be stuck in the classic ORM problem and don't know how to handle it, so any help is welcome at this point.

Is there a way to get a pivot table in a hasManyThrough query? Better yet, apply some sort of filter or sort to it. Typical example

Table products

id,title 

Table categories

 id,title 

table products_categories

 productsId, categoriesId, orderBy, main 

So, in the above scenario, let's say you want to get all product categories X (main = true) or you want to sort product categories using orderBy .

What is happening now is the first SELECT for products to get product data, the second SELECT on products_categories to get categoriesId and the final SELECT by category to get actual categories. Ideally, filters and sorting should apply to 2nd SELECT , e.g.

 SELECT `id`,`productsId`,`categoriesId`,`orderBy`,`main` FROM `products_categories` WHERE `productsId` IN (180) WHERE main = 1 ORDER BY `orderBy` DESC 

Another typical example would be the desire to order product images depending on which user wants them

so you will have a products_images table

 id,image,productsID,orderBy 

and you want

 SELECT from products_images WHERE productsId In (180) ORDER BY orderBy ASC 

Is it possible?

EDIT: Here is the link needed for the staging table to get what I need based on my schema.

 Products.hasMany(Images, { as: "Images", "foreignKey": "productsId", "through": ProductsImagesItems, scope: function (inst, filter) { return {active: 1}; } }); 

Thing is a scope function giving me access to the final result, not to the staging table.

Answer 1

I am not sure to fully understand your problem (s), but you definitely need to move away from the concept of the table and express your problem in terms of models and relationships.

As I can see, you have two Product models (properties: name) and Category (properties: main).

Then you can have a relationship between the two, potentially

• Product belongsTo Category
• Category hasMany Product

This means that the product will belong to one category, and the category may contain many products. There are other relationships available.

Then, using the generated REST API, you can filter GET requests to receive elements depending on their properties (for example, main in your case) or use custom GET requests (automatically generated when adding relations) to get an instance of all products related to a specific categories.

Does it help?

Answer 2

Based on what you have here, I would recommend using the scope parameter when defining a relationship. LoopBack docs show a very similar example of a “product category” scenario :

 Product.hasMany(Category, { as: 'categories', scope: function(instance, filter) { return { type: instance.type }; } }); 

In the above example, instance is the category that is being mapped, and each product will have a new categories property that will contain the corresponding Category objects for this Product . Please note that this does not match your exact data scheme, so you may need to play with it. Also, I think your API request will need to indicate that you want the related categories data to be loaded (they are not included by default):

/api/Products/13?filter{"include":["categories"]}

Answer 3

I suggest you define a user / remote method in Product.js that does the job for you.

 Product.getCategories(_productId){ // if you are taking product title as param instead of _productId, // you will first need to find product ID // then execute a find query on products_categories with // 1. where filter to get only main categoris and productId = _productId // 2. include filter to include product and category objects // 3. orderBy filter to sort items based on orderBy column // now you will get an array of products_categories. // Each item / object in the array will have nested objects of Product and Category. }