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.
