Thanks a lot Niphlod. I will modify and code to match that.
Thanks again.
El jueves, 2 de enero de 2014 15:26:27 UTC-6, Niphlod escribió:
>
> Given your expected resultset, first and foremost the price_list table is
> "out of the picture".
>
> That's because you want just all the fields from products (id, name,
> description), "attach" the price from product_prices (but you NEED to
> choose what price (default or discount), else you'd have multiple lines of
> the same product).
> Then again you want either none or just the first image of product_images.
>
> groupby isn't the answer, because you're discarding large "chunks of
> universe". And boy, you chose a really complicated datamodel.
>
> the base "universe" query would be
> db(
> (db.products.id == db.product_prices.product_id) &
> (db.product_prices.price_list_id == db.price_lists.id) &
> (db.price_lists.name == 'Default price')
> ).select(db.products.id, db.products.name, db.products.description, db.
> product_prices.price)
>
>
>
> This gives you all but the images. You should then store in a variable
> (product_ids) the list of the products id to fetch the images separately.
> Assuming you'd like to fetch only the first recurrence of an image for a
> given product, this should work
>
> first_image = db.product_images.id.min() #the image with the id that
> comes first is the min() of the set
> images = db(db.product_images.product_id.belongs(product_ids)).select(
> first_image, groupby=db.product_images.product_id)
> image_dict = {}
> for row in images:
> image_dict[row.product_images.product_id] = db.product_images(
> first_image)
>
>
>
> now in image_dict you have a dict holding for each product_id the row of
> the first image in the product_images table.
>
> This basically means multiple queries to fetch what you want.
>
> Given that the thread started with you preferring to fetch this set out of
> a single query, I'd advise you to add either a boolean to the
> product_images table that states "main_image" (true/false) or an integer
> "image_index" that will hold 1,2,3,4 etc to be able to filter the data
> accordingly.
>
> in the "boolean" case, your "image" query would be much simpler
>
> db(
> (db.product_images.product_id.belongs(product_ids)) &
> (db.product_images.main_image == True) # or image_index == 1
> ).select()
>
>
>
> This would mean two selects to fetch what you want. From there, combining
> the two in a single select with a left join shouldn't be hard
>
> BTW (for the kickass t-sql guys): postgresql has window
> functions<http://www.postgresql.org/docs/9.3/static/tutorial-window.html>to
> make these kind of complicated grouping/slicing work (fast), but:
> - those syntaxes are quite backend specific
> - only a subset is supported and only on a bunch of adapters (mssql has
> most of them in 2008, all in 2012, mysql has very limited, oracle and
> postgres in recent versions)
> so DAL doesn't expose native functions supporting them.
> The syntax is pretty complicated, e.g. :
> select foo(field) over (partition by list_of_fields order
> bylist_of_other_fields
> )
> from table
> and needs some careful planning, although the results are really (really)
> fast, compared to what it would need with "standard" multiple queries.
> Making a view out of a complicate query and accessing it with
> migrate=False is viable and recommended (only con: your app will probably
> be able to run on the single backend you choose),, but you need to be a
> sql-fu master.
>
> That being said, the speedup is noticeable for the end user if you need
> thousands of rows grouped/sliced. For the usual webpage showing at most a
> hundreds product fetching the resulset with the above two queries doesn't
> practically make a difference.
>
--
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
