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?
