Joins & Sorting

[zohaibAsif-tes]

REQUIREMENT:

1. I want to join products and product variants and sort (asc/desc) the products by the price of product variants.

QUESTIONS(S):

1. Is this possible in Odoo?

[zohaibAsif-tes]

@ahuret Can you please help me with this?

[ahuret]

@zohaibAsif-tes Hello :wave: About Joins, as I know, there's no builtin way to do it with Odoo Xmlrpc API.

Here is what I suggest instead:

• I guess you're talking about product.template and product.supplierinfo models
• You can retrieve all supplierinfos of one product and sort them by price with like this

package main

import (
    "fmt"
    "log"

    odoo "github.com/skilld-labs/go-odoo"
)

func main() {
    c, err := odoo.NewClient(&odoo.ClientConfig{
        Admin:    "XXX",
        Password: "XXX",
        Database: "XXX",
        URL:      "XXX",
    })
    if err != nil {
        log.Fatal(err)
    }

    // here it will fetch product with ID 1 but instead add any logic you want
    product, err := c.GetProductTemplate(1)
    if err != nil {
        log.Fatal(err)
    }

    variants, err := c.FindProductSupplierinfos(
        odoo.NewCriteria().Add("id", "in", product.VariantSellerIds.Get()),
        odoo.NewOptions().Add("order", "price desc"),
    )
    if err != nil {
        log.Fatal(err)
    }
    for _, v := range *variants {
        fmt.Printf("%v price: %v\n", v.DisplayName.Get(), v.Price.Get())
    }
}

Tell me if it help and answer your question :-)

[zohaibAsif-tes]

Thank you so much for the quick response @ahuret.

• I am talking about product.template & product.product models.
• I have more than 1000 products in my Odoo store and each have different variants. So fetching all the products first and then getting variants will make the API slow and this will not work with pagination.
• Another problem with this approach is that this will only sort the variants but I want to sort the main products by the prices inside variants.

I have the following response structure:

[
 {
  "name": "Main Product 1,
  "variants": [
    {
     "name": "15 cm",
     "price": "20"
    },
    {
     "name": "25 cm",
     "price": "30"
    },...
   ]
  },
 {
  "name": "Main Product 2,
  "variants": [
    {
     "name": "30 cm",
     "price": "10"
    },
    {
     "name": "40 cm",
     "price": "20"
    },...
   ]
  },...
]

If joins are not supported with the API, is it possible to run raw SQL query?

EDITED

I have searched and found a way to run raw SQL query:

self.env.cr.execute(sql_query) (python code)

• self: current model instance
• env: odoo's environment

• cr: database cursor object

  I am now confused what will be the cr: database cursor object in this odoo client?

[ahuret]

I have more than 1000 products in my Odoo store and each have different variants. So fetching all the products first and then getting variants will make the API slow and this will not work with pagination.

About this, if you want to not overload your server and reduce the size of the request, you can ask to return only the models attributes you want, using Attributes option.

Another problem with this approach is that this will only sort the variants but I want to sort the main products by the prices inside variants.

About this, I'm not sure to understand what you mean, you want to sort the main products based on what aggregation of prices inside variants ? Also, you still can construct your own custom structure and use native Golang sort methods.

I have searched and found a way to run raw SQL query:

It's not possible to run SQL queries with Xmlrpc API, if you want SQL queries you need to use custom odoo models, that's different.

[zohaibAsif-tes]

About this, if you want to not overload your server and reduce the size of the request, you can ask to return only the models attributes you want, using Attributes option.

I am already doing this but still the response will get slow because I am mapping this response to another structure and adding some things to it on my side.

About this, I'm not sure to understand what you mean, you want to sort the main products based on what aggregation of prices inside variants ? Also, you still can construct your own custom structure and use native Golang sort methods.

product.template - main product listing product.product - variant of a product

Let me state the whole problem:

• I have a listing of products in UI (let's call this main listing), when a user clicks on any product another window opens with the listing of all the variants (different sizes & prices) the product has.
• In UI, the user can filter the products in the main listing using size and price in any order (asc/desc).
• The size and price are not present in the product model because each variant has a different size and price.
• The price and size are present in the variants.
• When a user applies a filter I have to sort the main product listing by using the price/size present in its variants.

I hope this will help in understanding the problem, if it doesn't, please feel free to ask anything.

[ahuret]

I am already doing this but still the response will get slow because I am mapping this response to another structure and adding some things to it on my side.

I don't see how it is related with the request you will do on Odoo server.

About your process, I don't get:

Say you have :

Product A: Variant 1: Price: 10 Variant 2: Price: 20

Product B: Variant 3: Price: 5 Variant 4: Price: 30

If you want to order by price ASC, what do you want ?

have Product B first (cause Variant 3 is the min) ? in this case, the aggregation for sorting is MIN and MAX functions, is that what you want to do ?

[ahuret]

@zohaibAsif-tes :wave: I'm closing this issue, feel free to re-open it if you think there's an issue with the library.