Re: [web2py] SQL query to web2py code

Hi Massimiliano, Thanks for your helpful reply. print db(db.ntw_edge.outID==1147)._select(left=db.ntw_edge.on(db. > ntw_edge.inID==alias_edge.outID)) > Web2py generates: SELECT alias_edge.outid, alias_edge.inid, alias_edge.label, ntw_edge.outid, ntw_edge.inid, ntw_edge.label FROM ntw_edge

Re: [web2py] SQL query to web2py code

Try to print the sql generated from web2py: print db(db.ntw_edge.outID==1147)._select(left=db.ntw_edge.on(db. ntw_edge.inID==alias_edge.outID)) On Thu, Jun 9, 2016 at 10:10 AM, 'Annet' via web2py-users < web2py@googlegroups.com> wrote: > I have the following SQL query (in Postgresql): > > >

[web2py] SQL query to web2py code

I have the following SQL query (in Postgresql): SELECT ntw_edge.outid, ntw_edge.inid, ntw_edge.label, alias_edge.inid, alias_edge.outid, alias_edge.label FROM ntw_edge LEFT OUTER JOIN ntw_edge AS alias_edge ON ntw_edge.inid=alias_edge.outid WHERE ntw_edge.outid=1147; In web2py I have: def

[web2py] sql query

Hi could you explain me why it does not work : my file db.py (a part of file) loginsession = auth.user.username dbRH = DAL('mysql://xxx:xxx@localhost/RH', pool_size=0) dbRH.define_table('test', SQLField('login', type = 'string' , length = 20 ), SQLField('nom', type = 'string' , length =

[web2py] SQL query equivalent in DAL

Hello Forum, Please help me in getting the equivalent DAL query for the following SQL query. I intend to pass this on to SQLFORM.grid Select * from db.pc where db.pc.id not in (select unique db.assigned.computer from db.assigned) Web2Py Version 2.9.12-stable+timestamp.2015.02.15.23.04.25

Re: [web2py] SQL query equivalent in DAL

db(~db.pc.id.belongs(db(db.assigned.id > 0)._select(db.assigned.id )).select(db.pc.ALL) Richard On Fri, Sep 4, 2015 at 3:35 PM, P T wrote: > > Hello Forum, > > Please help me in getting the equivalent DAL query for the following SQL > query. I intend to pass this on

Re: [web2py] SQL query equivalent in DAL

On Friday, September 4, 2015 at 1:37:07 PM UTC-7, Anthony wrote: > > > (see, no "Field('name', 'reference othertable', ...)") >> > > Field('computer', db.pc) > > This is a reference to the "pc" table. > > Ooops! /dps "Location, location, location" -- Resources: - http://web2py.com -

Re: [web2py] SQL query equivalent in DAL

Thank you Richard, your expression returns ROWS and can't be used in SQLFORM.grid. When I removed the last select db(~db.pc.id.belongs(db(db.assigned.id > 0)._select(db.assigned.id)) then I get File "E:\web2py 2.9.12\gluon\sqlhtml.py", line 2139, in grid field_id = tables[0]._id IndexError:

Re: [web2py] SQL query equivalent in DAL

I'm puzzled about the why ... On Friday, September 4, 2015 at 12:42:32 PM UTC-7, Richard wrote: > > db(~db.pc.id.belongs(db(db.assigned.id > 0)._select(db.assigned.id > )).select(db.pc.ALL) > > Richard > > > > On Fri, Sep 4, 2015 at 3:35 PM, P T > wrote: > >> >> Hello

Re: [web2py] SQL query equivalent in DAL

> (see, no "Field('name', 'reference othertable', ...)") > Field('computer', db.pc) This is a reference to the "pc" table. Anthony -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) -

Re: [web2py] SQL query equivalent in DAL

Got it. I need to remove the initial db to make that into a query (~db.pc.id.belongs(db(db.assigned.id > 0)._select(db.assigned.id)) On Friday, September 4, 2015 at 3:37:50 PM UTC-5, P T wrote: > > Thank you Richard, your expression returns ROWS and can't be used in > SQLFORM.grid. When I

[web2py] SQL query to find all records which are not in another table

I'm just a little bit confusing because I can't make correct selection to find records that are absent in another (referenced) table. It's clear to me how to make inner join: rows = db(db.persons).select(join=db.tbl.on(db.persons.id==db.tbl.person), groupby=db.persons.f_name,

[web2py] sql query efficency implementation

I'm trying to get a row count on a query that joins together every instance of a coalesce set of fields and coming unstuck. The query in sql looks like: select sum(1) from (select count(direction) from tracking where direction = 1 AND left(created,10) = '2014-06-13' group by

[web2py] SQL Query Help Using DAL

I am trying to display chat message information; I have 2 tables The first table is the auth table define in web2py The second is this db.define_table('messages', Field('from',db.auth_user), Field('to',db.auth_user), Field('msg','string'),