Complicated question on recordsets

Results 1 to 3 of 3

Thread: Complicated question on recordsets

  1. #1
    Ben Roy Guest

    Default Complicated question on recordsets

    Well this is only sorta about recordsets. I&#039ve run into a problem with a search page and need a little input. Basically the issue at hand is that the client wants the results sorted by a set of "categories." My first thought was to use data shaping to pull each category and then the children. The problem with this is the way the categories are defined. For instance one of the categories to group results in is "Sales and Marketing Materials" but in the db the categories "sales materials" and "marketing materials" are stored separately.<BR><BR>That may be something that I can get around with data shaping, but it gets worse. Some of the display categories aren&#039t stored in the category field. Some of them pull from a document type field, some from a classification field, etc.<BR><BR>Bottom line, I think I&#039m just going to need to grab a recordset with all the results, then filter for each category and display the matching results for each category. That&#039s where my question comes in. What&#039s the best way to do this? I would think the worst way to do it would be to grab a recordset for each category that specified the category criteria in the query. Please give some thoughts on the best way to do this.

  2. #2
    SPG Guest

    Default Possible solution (Paragraph 3)

    Get a large weapon -- a harpoon is nice -- and hunt down the designer (db or web) that locked you into that setup. Let them be accountable for their mess.<BR><BR>Seriously, for a query as frequent as a site search, you shouldn&#039t be having to do much data shaping at all. Generally SQL which is slow to write is slow to run.<BR><BR>As a side suggestion (without completely envisioning what you&#039re doing), could you add a db table with an entry for each searchable category (primary key) with an associated chunk of query? For example<BR><BR>"Sales & Marketing Whatnot" = "(sCategory = &#039Sales&#039) or (sCategory = &#039Marketing&#039)"<BR><BR>and then you could copy chunks of SQL into your query without constantly splicing data on query. (Yes, this solution would give you two queries instead of one -- but I think it&#039s got better scalability than trusting your categories to be set in stone...)

  3. #3
    Ben Roy Guest

    Default RE: Possible solution (Paragraph 3)

    If I had any choice in the matter I would have completely redesigned the db for this...but we didn&#039t have the option. <BR><BR>In any case, I have done that exact thing for other parts of the site and was planning on doing something similar here. They have all sorts of parts of the site that don&#039t follow the structure of the DB, even though the db was designed ONLY to support the website. The real question though is what to do with those queries.<BR><BR>Since I posted I&#039m thinking it will look like this:<BR><BR>For Each Category<BR> rst.Filter = Category.Query<BR> While Not Rst.EOF<BR> Print Item<BR> Movenext<BR> Wend<BR>Next Category<BR><BR>Is that a reasonable thing to do? I&#039m not sure what the cost of using .Filter is, but I imagine that with a static cursor this should perform reasonably well. This was more the question I was asking, but I understand it would be hard to get from the initial question.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts