Forum OpenACS Development: SQL queries 'n TCL

Iuri, what you need is to do a subselect. This is where you take the 2 queries and combine them, returning only the results you want. The method you mention will be extremely slow.

If on the other hand, you need to have the user select from a list (from the first query) and then return information based on what the user chooses, it is better to have them first choose the organization_ids , then go ahead and do the query with the organization_ids as a list.

Does that answer your question?

The full documentation on subqueries can be found at http://www.postgresql.org/docs/current/static/functions-subquery.html

You can do the following:

select * from organizations where organization_id in (select organization_id from yourtable where user_id = :foo)

Hope this helps

The select i did is a litle bit tricky, since my organization pkg is set up as non-singleton, so i need to pass the package_id and organization_id in the query to get each organization of each package the user owns.

the query:
?xml version="1.0"?
queryset
fullquery name="orgs_query"
querytext
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 in (SELECT ow.organization_id
FROM organization_owner ow
WHERE user_id = :user_id)
AND
o.organization_id = tm.organization_id and
o.packages = :(SELECT ow.package_id
FROM organization_owner ow
WHERE user_id =: user_id)
tm.organization_type_id = ot.organization_type_id and
[template::list::filter_where_clauses -and -name "orgs"]
[template::list::orderby_clause -orderby -name orgs]
/querytext
/fullquery

The attribution is incorrectly made
o.packages = :(SELECT ow.package_id
FROM organization_owner ow
WHERE user_id =: user_id)

MAlte,
I haven't found anything similar to this on the link you post above. Although it's a very good source, thanks a lot.

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]