Re: [web2py] query in DAL

[Ovidio Marinho]

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.date) = 2010)
>GROUP BY date, items
> ) AS foo, stock
> WHERE
>stock.items = foo.items
> GROUP by date
>
> Any help would be appreciated.
>
> thanks!
>



-- 
Ovidio Marinho Falcao Neto
 ovidio...@gmail.com
 Tecnologia da Informaçao
 Casa Civil do Governador
 83 3214 7885 - 88269088
  Paraiba

Re: [web2py] query in DAL

[beto (R3)]

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, Vasile Ermicioi  wrote:
>     _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

Re: [web2py] query in DAL

[Vasile Ermicioi]

_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

Re: [web2py] query in DAL

[beto (R3)]

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 a single query :(..

Hope I made my point..

thanks again!


On Fri, Jan 28, 2011 at 7:59 PM, Vasile Ermicioi  wrote:
> 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)

Re: [web2py] query in DAL

[Vasile Ermicioi]

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)

Re: [web2py] query in DAL

[beto (R3)]

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 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 rows:
>     print row['date'], row['total']

Re: [web2py] query in DAL

[Vasile Ermicioi]

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 rows:
print row['date'], row['total']

[web2py] query in DAL

[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.date) = 2010)
GROUP BY date, items
) AS foo, stock
WHERE
stock.items = foo.items
GROUP by date

Any help would be appreciated.

thanks!