OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

get array of table sqlalchemy

  • Thread starter Thread starter Andrey
  • Start date Start date
A

Andrey

Guest
I want to get the product table data array using array_agg function. In Postgers this works great, but in sqlalchemy this can only be done with data types integer, string, and so on. How to implement this in sqlalchemy?

Code in postgresql:

Code:
   select DISTINCT
        seller.title, array_agg(product), COUNT(product.id)
        from seller_product
            INNER JOIN seller ON seller.ozon_id = seller_product.id_seller 
            INNER JOIN product ON product.ozon_id = seller_product.id_product 
        WHERE start_id = 36
        GROUP BY seller.title ORDER BY COUNT(product.id) DESC

Code python. I'm trying to do it like this:

Code:
slct_stmt_now = select(Seller, func.array_agg(Product.__table__), func.count(Product.id)).distinct().select_from(seller_product)
    slct_stmt_now = slct_stmt_now.join(Seller, Seller.ozon_id == seller_product.columns["id_seller"]).join(Product, Product.ozon_id == seller_product.columns["id_product"]).where(seller_product.columns["start_id"] == LAST_START_ID).group_by(Seller)
now_data_txt = session.execute(slct_stmt_now.order_by(func.count(Product.id).desc())).all()
<p>I want to get the product table data array using array_agg function. In Postgers this works great, but in sqlalchemy this can only be done with data types integer, string, and so on. How to implement this in sqlalchemy?</p>
<p>Code in postgresql:</p>
<pre><code> select DISTINCT
seller.title, array_agg(product), COUNT(product.id)
from seller_product
INNER JOIN seller ON seller.ozon_id = seller_product.id_seller
INNER JOIN product ON product.ozon_id = seller_product.id_product
WHERE start_id = 36
GROUP BY seller.title ORDER BY COUNT(product.id) DESC
</code></pre>
<p>Code python. I'm trying to do it like this:</p>
<pre><code>slct_stmt_now = select(Seller, func.array_agg(Product.__table__), func.count(Product.id)).distinct().select_from(seller_product)
slct_stmt_now = slct_stmt_now.join(Seller, Seller.ozon_id == seller_product.columns["id_seller"]).join(Product, Product.ozon_id == seller_product.columns["id_product"]).where(seller_product.columns["start_id"] == LAST_START_ID).group_by(Seller)
now_data_txt = session.execute(slct_stmt_now.order_by(func.count(Product.id).desc())).all()
</code></pre>
 
Top