Your model is not saving somewhere the company_id related to a sale. (i.e.
if multiple users have the same company, then this query won't return all
the sales of the given company)
If "all data in company" means "all data of the current user" then it's
easy.
db(
(db.auth_user.id == auth.user_id) &
(db.sale.created_by == db.auth_user.id) &
(db.company.id == db.auth_user.company_id)
# & (db.product.id == db.sale.product_id) ##just if you want to fetch
product details
).select()
If instead means "all data of the company the current user belongs to" then
it's easier to split it in two queries
company_id = db(db.auth_user.id == auth.user_id).select().first().company_id
db(
(db.auth_user.company_id == company_id) &
(db.sale.created_by == db.auth_user.id) &
(db.company.id == db.auth_user.company_id)
# & (db.product.id == db.sale.product_id) ##just if you want to fetch
product details
).select()
On Friday, February 1, 2013 7:45:35 AM UTC+1, 黄祥 wrote:
>
> 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.
