[RESOLVED] nested data query

[leatherback]

Hiya All,

Got a little query problem.

I am building a recipe website (www.cookseasonal.com). The front page should display a list of categories. Each catergory could have all other categories as subcategory. Data is stored as:

Category_to_recipe
[ca_nr][ca_re_nr]

Category
[ca_id][ca_name]

recipe
[re_id][..]

What I would like is to create a listing of all categories, with a count of the number of recipes in that category, and for each category a listing of all sub-cat listings (which is what I have now used for a temp front page listing).

Obviously I can

 'select distinct ca_name, count(ca_nr) 
from category 
left join category_to_recipe on ca_id=ca_nr 
group by ca_re_nr';

and then do the same query for each individual category to create the sub-listing. However that sounds to me like too many query.

Anybody here that can help me do this in one query?

Cheers,

J

[drew010]

does seem like it could get a bit intense to calculate upon each visit and since it may require lots of queries it wouldn't be too efficient. maybe getting the counts and caching them somewhere else (db, file) periodically it would be more efficient when the clients hit the page.

i think due to the recursive nature of the categories there isn't a great way to do it with queries. alternatively i suppose you could just do one query to get a count of all different categories and their children and then get that in an array and use php code to work out all the counts rather than the db engine.

good luck with the site

[leatherback]

drew010 wrote:

maybe getting the counts and caching them somewhere else (db, file) periodically it would be more efficient when the clients hit the page.

Yeah, sounds like that could work. I am doing a similar thing for my lookup tables, which are cached in serialized arrays on the server, and only change when the database is updated. Not ideal though...

drew010 wrote:

alternatively i suppose you could just do one query to get a count of all different categories and their children

Hm.. Cannot get my head around how I would query that in one go?

[drew010]

i was sort of thinking along the lines of something i did recently which would let a person create a category tree where parent categories can have unlimited children that can map to more than one parent and we had a field that indicated the parent category, or 0 if it was a top level, and that way i could just do one query to get everything and loop through building different arrays so i could then create a full map as to the alternative of doing nested queries which when tested started to number very high with around 30 different child categories mapped to 2 or 3 each.

anyway im rambling but if you can somehow just do one query to get all the recipes and what category they are in and be able to map the categories to the parents then i think you could get a count. may be more trouble than its worth if you could just easily save the numbers and do a recount every so often

[leatherback]

Yeah, that sounds like the way I build my navigation structures. The main problem is the need to specify parents etc, which in this case would effectively exponentially increase the table sizes & number of inserts upon edits (As each inserted new category would result in an parent & child node for all other cats).

Hm.. Guess there is no easy query-solution, using nested queries and/or smart joining of tables.

[leatherback]

ok, I got a bit further with this. I created a test database with short names to test this. This looks like it could work, but I am not sure whether I am missing an important error:

select t3.lab1 as maincat, t4.lab1 as subcat, count(t1.recnr) as t1reccount, t1.catnr as t1cat, t2.catnr as t2cat 
from lnk as t1
  left join lnk as t2 on t1.recnr = t2.recnr
  left join cats as t3 on t1.catnr = t3.id1
  left join cats as t4 on t2.catnr = t4.id1
where t1.catnr <> t2.catnr
group by t2cat, t1cat