Forum OpenACS Development: Re: SQL queries 'n TCL

Collapse
7: Re: SQL queries 'n TCL (response to 1)
Posted by Iuri Sampaio on 04/13/07 11:14 PM
So far,
the problem is solved. Easily than i expected!!

fullquery name="orgs_query"
querytext
SELECT
ow.organization_id,
ow.package_id,
o.organization_id,
o.name,
o.legal_name,
o.reg_number,
o.notes,
ot.type as organization_type
FROM
organization_owner ow,
organizations o,
organization_types ot,
organization_type_map tm
WHERE
ow.user_id = :user_id and
o.organization_id = tm.organization_id and
tm.organization_type_id = ot.organization_type_id and
o.package_id = ow.package_id
[template::list::filter_where_clauses -and -name "orgs"]
[template::list::orderby_clause -orderby -name orgs]
/querytext
/fullquery

thanks a lot for the feedback

Collapse
8: Re: SQL queries 'n TCL (response to 7)
Posted by Iuri Sampaio on 04/16/07 11:34 PM
I just found out the query does not filter by package_id. So, orgs created by users in another org package instance, is not select to the users own orgs list.

SELECT o.organization_id,
o.name,
o.legal_name,
o.reg_number,
o.notes,
ot.type as organization_type
FROM organizations o,
organization_types ot,
organization_type_map tm
WHERE
o.organization_id = tm.organization_id and
o.organization_id in (SELECT ow.organization_id,ow.package_id
FROM organization_owner ow
WHERE ow.user_id = :user_id) and
tm.organization_type_id = ot.organization_type_id and
o.package_id =
[template::list::filter_where_clauses -and -name "orgs"]
[template::list::orderby_clause -orderby -name orgs]
Collapse
9: Re: SQL queries 'n TCL (response to 8)
Posted by Iuri Sampaio on 04/16/07 11:42 PM
It's done!
PAtrick and Malte,
It's really great the subselect you two mentioned on this thread.


SELECT o.organization_id,
o.name,
o.legal_name,
o.reg_number,
o.notes,
ot.type as organization_type
FROM organizations o,
organization_types ot,
organization_type_map tm
WHERE
o.organization_id = tm.organization_id and
o.organization_id in (SELECT ow.organization_id
FROM organization_owner ow
WHERE ow.user_id = :user_id) and
tm.organization_type_id = ot.organization_type_id and
o.package_id in (SELECT ow.package_id
FROM organization_owner ow
WHERE ow.organization_id = o.organization_id)

Thanks for the link to the docs. I didn't find before on PG website