I still do not fully understand so I am making some assumptions.
Moreover you use field names either plural (and to me make more sense
singular) or not allowed (date). So...
db.define_table('item',Field('name',format='%(name)s')
db.define_table('stock',Field('item',db.item),Field('type'))
db.define_table('log',Field('item'),Field('sell_date','date'))
sold_last_year = db.log.sell_date.year()==2010
books = db.stock.type=='book'
stocked_as_books =
db.log.item.belongs(db(books)._select(db.stock.item))
rows = db(stocked_as_books)
(sold_last_year).select(db.log.sell_date,db.log.id.count(),groupy=db.log.sell_date)
for row in rows:
print row.log.sell_date, row[db.log.id.count()]
SELECT
date, count(items)
FROM
(
SELECT
logs.date, logs.items
FROM logs
WHERE
extract(year from logs.date) = 2010)
GROUP BY date, items
) AS foo, stock
WHERE
stock.items = foo.items AND
stock.type = 'Books' AND ...
GROUP by date
On Jan 29, 10:35 am, "beto (R3)" <bet...@gmail.com> wrote:
> Hey Vasile:
>
> Cause I have more clauses based on the stock table that I haven't
> included in the original query. For example:
>
> SELECT
> date, count(items)
> FROM
> (
> SELECT
> logs.date, logs.items
> FROM logs
> WHERE
> extract(year from logs.date) = 2010)
> GROUP BY date, items
> ) AS foo, stock
> WHERE
> stock.items = foo.items AND
> stock.type = 'Books' AND ...
> GROUP by date
>
> cheers,
>
>
>
>
>
>
>
> On Sat, Jan 29, 2011 at 1:27 PM, Vasile Ermicioi <elff...@gmail.com> wrote:
> > why do you need 'stock' table ?
> >>> WHERE
> >>> stock.items = foo.items
