I am doing an inventory system, you could help me with some example?
2011/1/28 beto (R3)
> Hey guys:
>
> Is there a way to do this query in DAL?
>
> SELECT
>date, count(foo.items)
> FROM
> (
>SELECT
>logs.date, logs.items
>FROM logs
>WHERE
>extract(year from logs.
Hey Vasile:
The end select would give an error.. belongs() translates to a:
" db.logs.item IN ( SELECT db.logs.date, db.logs.item [..] )"
"ERROR: subquery has too many columns"
If the tables had id fields this technique could actually work tho...
thanks!
beto
On Sat, Jan 29, 2011 at 4:13 AM
_sql1 = db(db.logs.date.year()==2010)._select(db.logs.date,
db.logs.item, groupby=db.logs.item)
rows = db(db.logs.item.belongs(_sql1) & (db.stock.item ==
db.logs.item)).select(db.logs.date,db.logs.item.count(),
groupby=db.logs.date)
notice _select for the first query
Hey:
Nope.. that won't work.. that puts everything in a single query.. The
reason I'm doing SELECT ... FROM ( SELECT.. GROUP BY ..) is to get rid
of multiple items in the same day. That's why I group by date, items
and then group by date.
If I could do a count(distinct(items)) I could do that in
perhaps
db((db.logs.date.year()==2010) & (db.stock.item ==
db.logs.item)).select(db.logs.date,db.logs.item.count(),
groupby=db.logs.date)
Hey Vasile:
Thanks for your answer.. yes.. that I know and that's actually what
I'm doing.. but I'd like to use the benefits of doing it with DAL.
(cache the queries, security validation for parameters, etc).
regards.
On Fri, Jan 28, 2011 at 7:18 PM, Vasile Ermicioi wrote:
> you always can exec
you always can execute sql
sql_str = 'SELECT * FROM ...'
rows = db.executesql(sql_str)
but your results will not be objects, but an array of arrays or an array of
dicts if you do
sql_str = '''SELECT date, count(foo.items) as total ... '
rows = db.executesql(sql_str, as_dict = True)
for row in
Hey guys:
Is there a way to do this query in DAL?
SELECT
date, count(foo.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
GROUP by date
Any he
8 matches
Mail list logo
