How to implement limit based on related items

[jfayad]

When setting up the PIM with variants as described here: https://openpim.org/university/data_model/01_Types.html#dealing-with-variants

It is not clear how can we run a search that will filter items on both levels.

For example I have a variant that carries a price and the product under each carries a color. If I want to get the products of a specific color within a specific price range there's no clear way to get that with one single query to which I can apply a clear pagination (with the limit and offset params).

Is there an undocumented way to run such a query using the graphQL API ?

[jfayad]

While waiting for an answer I developed a workaround in my own code that is sub optimal IMO. it requires loading more records than necessary from the API to perform an accurate pagination...

This could be relatively simple to solve on the API by enabling join and where clause on the joined record

[openpim]

Hi @jfayad,

First of all, you can easy use direct DB access to read the data, this can be very efficient way if you just want to read it. For sure, you should not use direct DB access to write the data, but read is fine. Sometimes when we need to read the data and then to update it, we are using direct DB access to read the data and then writing the data through API.

Another way to make this is to use SQL code inside your API queries. You can write something like this:

{ values: {color: 'black'}, OP_and: "###:EXISTS (select p.id from items p where p.values->>'price' = 100 and p..\"parentIdentifier\"=\"Item\".\"identifier\" )" }

if string starts with ###: this means that all rest is going directly to SQL without changes. And here we have SQL condition where we are finding variant (record that has parentIdentifier like our product identifier) with right price.

[jfayad]

Thank you for the tip about the direct SQL query but I want to avoid hitting the DB directly as the schema could eventually change with a future update which would make the code obsolete.

The trick with the triple hashtags is interesting, it should definitely be documented in the operator page :-)