Grazie. This unlocks a door. I thought I had to repeat the call to left() same as the SQL.
On Oct 6, 5:13 pm, Massimo Di Pierro <massimo.dipie...@gmail.com> wrote: > Here is your SQL: > > SELECT purchase_orders.issue_date, purchase_orders.number, > products.name > FROM purchase_orders LEFT JOIN purchase_order_products ON > purchase_order_products.purchase_order_id = purchase_orders.id LEFT > JOIN products ON purchase_order_products.product_id = products.id > > web2py-ese: > > db().select(db.purchase_orders.issue_date, db.purchase_orders.number, > db.products.name, > left=[db.purchase_order_products.on(db.purchase_order_products.purchase_order_id > == db.purchase_orders.id), > db.products.on(db.purchase_order_products.product_id == > db.products.id)]) > > On Oct 6, 1:46 pm, Cliff <cjk...@gmail.com> wrote: > > > > > Actually 'name' comes from products. > > > Maybe it would be clearer this way: > > SELECT purchase_orders.issue_date, > > purchase_orders.number, > > products.name > > FROM purchase_orders LEFT JOIN > > purchase_order_products ON purchase_order_products.purchase_order_id > > = > > purchase_orders.id LEFT JOIN products ON > > purchase_order_products.product_id = products.id > > > On Oct 6, 12:13 pm, Massimo Di Pierro <massimo.dipie...@gmail.com> > > wrote: > > > > Why do you left join products if you do not select anything from it? > > > > On Oct 6, 7:40 am, Cliff <cjk...@gmail.com> wrote: > > > > > Here is the model, simplified: > > > > > db.define_table('purchase_orders', > > > > Field('issue_date', 'date', > > > > comment = SPAN(a_comment, _style='color:orange;')), > > > > Field('number', length=24, requires=IS_NOT_EMPTY(), > > > > required=True, notnull=True, > > > > comment=SPAN('Required', _style='color:orange;')), > > > > Field('closed', 'boolean', readable=False, writable=False, > > > > default=False), > > > > format = '%(number)s' > > > > ) > > > > db.define_table( 'products', > > > > Field('name', length=256, required=True, notnull=True), > > > > Field('internal_item_number', length=36), > > > > format = '%(name)s' > > > > ) > > > > db.define_table('purchase_order_products', > > > > Field('purchase_order_id', db.purchase_orders), > > > > Field('product_id', db.products), > > > > Field('quantity', 'decimal(12,3)'), > > > > ) > > > > > in SQL I can make this query: > > > > SELECT issue_date, number, name FROM purchase_orders LEFT JOIN > > > > purchase_order_products ON purchase_order_products.purchase_order_id = > > > > purchase_orders.id LEFT JOIN products ON > > > > purchase_order_products.product_id = products.id > > > > > How do I do this in Web2py?