loopback relational database hasManyThrough pivot table

Question

I seem to be stuck on a classic ORM issue and don't know really how to handle it, so at this point any help is welcome.

Is there a way to get the pivot table on a hasManyThrough query? Better yet, apply some filter or sort to it. A typical example

Table products

id,title

Table categories

id,title

table products_categories

productsId, categoriesId, orderBy, main

So, in the above scenario, say you want to get all categories of product X that are (main = true) or you want to sort the the product categories by orderBy.

What happens now is a first SELECT on products to get the product data, a second SELECT on products_categories to get the categoriesId and a final SELECT on categories to get the actual categories. Ideally, filters and sort should be applied to the 2nd SELECT like

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 wanting to order the product images based on the order the user wants them to

so you would have a products_images table

id,image,productsID,orderBy

and you would want to

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

Is that even possible?

EDIT : Here is the relationship needed for an intermediate 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 the scope function is giving me access to the final result and not to the intermediate table.

Wistrup

What do you mean by intermediate table ? Like I said, you must forget about tables, loopback is providing abstraction on top of that. Either you want to read/write model's properties, or you want to identify related models (Images for inst.) from another model (Product n°5 for instance). – Nonlinearity

Answer 1

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

The way I see it, you have two models Product(properties: title) and Category (properties: main).

Then, you can have relations between the two, potentially

• Product belongsTo Category
• Category hasMany Product

This means a product will belong to a single category, while a category may contain many products. There are other relations available

Then, using the generated REST API, you can filter GET requests to get items in function of their properties (like main in your case), or use custom GET requests (automatically generated when you add relations) to get for instance all products belonging to a specific category.

Does this helps ?

Nonlinearity

Answer 2

Based on what you have here I'd probably recommend using the scope option when defining the relationship. The LoopBack docs show a very similar example of the "product - category" scenario:

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

In the example above, instance is a category that is being matched, and each product would have a new categories property that would contain the matching Category entities for that Product. Note that this does not follow your exact data scheme, so you may need to play around with it. Also, I think your API query would have to specify that you want the categories related data loaded (those are not included by default):

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

Shock

I have been going down that road but with no success. Thing is, i need to scope the intermediate table and the syntax you, me and the example presents gives access to the final model. I suppose one could filter out stuff at this point but it is not that efficient as you have already queried the DB. I have updated my questing giving a sample of the relationship needed to get the query to work – Wistrup

Answer 3

I suggest you define a custom / remote method in Product.js that does the work 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.
}

Paring

This is indeed one way of going about it but it defies the whole point of relationships in loopback. I was looking for a more "loopback" way of going about it. Truth be told, it can be done if your DB is Mongo or any NoSQL by using the referencesMany relationship but to do it in a SQL way it is kind of a hustle – Wistrup