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 <[email protected]> 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 <[email protected]>
> wrote:
>
>
>
>
>
>
>
> > Why do you left join products if you do not select anything from it?
>
> > On Oct 6, 7:40 am, Cliff <[email protected]> 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?
