- see footer for list info -<
Dave,
On 11/07/06, Dave Ashworth <[EMAIL PROTECTED]> wrote:
>- see footer for list info -<
I would like to perform a query of queries on a Verity Collection whilst
performing a join on a table within the database via the verity key
is this possible?
am struggling to find documentation if it is
my query goes along the lines:
<cfsearch name="site_search_initial"
collection="#request.verity_collection_page_items#"
type="simple"
criteria="#attributes.site_search_for#">
<cfquery name="site_search" dbtype="query">
select *
from site_search_initial, tbl_verity_page_items
where site_search_initial.key like tbl_verity_page_items.vpi_id
and tbl_verity_page_items.vpi_member like #session.logged_in#;
</cfquery>
Query of Query only works on one query object that is already stored
in memory, so you wouldn't be able to do a query of a verity search
result set with an external datasource.
You would probably have to do this in a few stages.
1) cfsearch to get your pages based on search criteria
2) do a query of the page items table using the keys from your
cfsearch and the user login id to get a shortened list of available
pages
3) use the list of keys from the query in a QofQ against the cfsearch
result to reduce your search results.
Point 1 you already have.
Point 2 :
<cfquery name="user_pages" datasource="...">
SELECT tbl_verity_page_items.vpi_id
FROM tbl_verity_page_items
WHERE tbl_verity_page_items.vpi_id IN (#valuelist(site_search_initial.key)#)
AND tbl_verity_page_items.vpi_member like #session.logged_in#
</cfquery>
Point 3:
<cfquery name="final_site_search" dbtype="query">
SELECT *
FROM site_search_initial
WHERE key IN (#valuelist(user_pages.vpi_id)#)
</cfquery>
That'll give you a query of your search results that are associated
with the currently logged in user.
The other thing you could do is to store the vpi_member field in one
of the custom fields in your verity collection. That way you can
reduce this to 2 steps, by doing a query of query using the login_id
against the custom field you stored it in.
Hope that helps
Stephen
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
