Hello Guys (by the way Happy New Year everyone). I'm having a problem writing a query to get product details, price and only one image for every product.
I have tables like this: *db.define_table('price_lists'* * Field('name')* * )* *db.define_table('products',* * Field('name'),* * Field('description'),* * ...)* *db.define_table('product_prices',* * Field('product_id',db.products),* * Field('price_list_id',db.price_lists),* * Field('price',type='decimal(14,6)')* * )* *db.defin_table('product_images',* * Field('product_id',db.products),* * Field('image',type='upload')* I was doing this with sqlite and mysql: *prod_query = (db.products.id==db.product_prices.product_id) &* * (db.product_prices.price_list_id==current_list_id)* *products = db(prod_query).select(left=db.product_images.on(db.products.id==db.product_images.product_id),* * groupby=db.products.id)* but now that we tested with postgres it just wont work, so I fixed the query with the following: *prod_query = (db.products.id==db.product_prices.product_id) &* * (db.product_prices.price_list_id==current_list_id)* *products = db(prod_query).select(left=db.product_images.on(db.products.id==db.product_images.product_id),* * groupby=db.products.id|db.product_prices.id|db.product_images.id)* but then if a product has two prices or images it will appear more than once. How can I fix it for postgres??? I have search the forum but haven't found anything like this. Thanks. -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.