hi,
how to create query for join multiple table?
my goal is to select all data in company and show it in invoice page.
*db.sale.created_by* refer to *db.auth_user.id*
*db.auth_user.company_id* refer to *db.company.id*
*
*
any suggestion, solutions or hints for this case?
*
*
here is the model that i've used and some of query that i've tested in
controller but have not work.
*# model :*
auth = Auth(db)
db.define_table('company',
Field('company_name', label=T('Company Name')),
Field('address', 'text', label=T('address')),
Field('zip', label=T('Zip')),
Field('city', label=T('City')),
Field('country', label=T('Country')),
Field('phone', label=T('Phone')),
Field('fax', label=T('Fax')),
Field('email', label=T('Email')),
Field('website', label=T('Website')),
auth.signature,
format='%(company_name)s')
auth.settings.extra_fields['auth_user']=[
Field('address', 'text', label=T('address')),
Field('zip', label=T('Zip')),
Field('city', label=T('City')),
Field('country', label=T('Country')),
Field('phone', label=T('Phone')),
Field('company_id', 'reference company', label=T('Company ID'))]
auth.define_tables(username=False, signature=False)
db.define_table('product',
Field('product_name', label=T('Product Name')),
Field('quantity', 'integer', label=T('Quantity')),
Field('unit_price', 'double', label=T('Unit Price')),
auth.signature,
format='%(product_name)s')
db.define_table('sale',
Field('invoice_no', label=T('Invoice No.')),
Field('product_id', 'reference product', label=T('Product ID')),
Field('quantity', 'integer', label=T('Quantity')),
Field('unit_price', 'double', label=T('Unit Price')),
Field('total_price', 'double', label=T('Total Price')),
Field('grand_total', 'double', label=T('Grand Total')),
Field('note', 'text', label=T('Note')),
auth.signature)
the controller i've tried to use but have not run :
*first*
rows= db((db.company.id==db.auth_user.company_id)&
(db.sale.created_by==db.auth_user.id)).select().first()
*second*
people_and_their_companies=db(db.auth_user.company_id==db.company.id)
row=people_and_their_companies(db.sale.created_by==request.args
(0)).select().first()
*third*
invoices_created_by=db(db.sale.invoice_no==request.args(0)).select(db.sale.created_by).first()
query=(db.auth_user.id==invoices_created_by)&(db.auth_user.company_id==db.company.id)
rows=db(query).select()
*fourth*
invoices_created_by=db(db.sale.invoice_no==request.args(0)).select(db.sale.created_by).first()
companies=db(db.company.id==db.auth_user.company_id)&(db.company.id==invoices_created_by.created_by).select().first()
thank you so much before
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
