I tried db(query).select(limitby = (0, number_of_records), orderby = '<random>', distinct = db.t_article['f_book_id']). It produces the error message:
<class 'psycopg2.ProgrammingError'> SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT DISTINCT ON (t_article.f_book_id) t_book_i... ^ groupby produces a comparable error message. I take it that it wants the t_article.f_book_id field passed to orderby, but I'd like to have orderby = "<random>". Any ideas on how to resolve this? On Wednesday, January 21, 2015 at 3:08:14 PM UTC-5, Niphlod wrote: > > seems like a job for distinct=True (or a groupby) . Docs are here > <http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#orderby--groupby--limitby--distinct--having-orderby_on_limitby-left-cache> > > On Wednesday, January 21, 2015 at 7:09:41 PM UTC+1, Spokes wrote: >> >> I'd like a db(query).select(...) operation to return a set of rows in >> which there can only be one instance of a row with a particular value for a >> certain field. My query is like this: >> >> book_query = ((db.t_article.f_book_id != None) & >> (db.t_article.f_book_id == db.t_book_image.id)) >> article_image_query = (db.t_article_image.f_article_id == >> db.t_article.id) >> query = [stuff pertaining to t_article] & (article_image_query | >> book_query) >> >> The idea is to find all t_article entries within a certain time frame >> (that's the stuff in brackets, which has been omitted), which either have >> an image associated with them, or which reference a t_book entry which has >> an image associated with it. >> >> The select statement is like this: >> >> rows = db(query).select(limitby = (0, number_of_records), orderby = >> '<random>') >> >> I'd like the select statement to return no more than one row with a >> particular value for the f_book_id field. With the above select() statement >> as it is, it's possible for rows[0]['t_article']['f_book_id'], >> rows[1]['t_article']['f_book_id'], and rows[2]['t_article']['f_book_id'], >> for example, to have the same value. Is it possible to modify the above >> code so that in situations like the preceding, only one of those rows >> (doesn't matter which one) is returned, and assigned to the rows object? >> 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/d/optout.
