Cats and Subcats

Problems:

Categories, Subcategories and each subcat has ads posted under it. The vanilla app side code supplied is too shoddy to use in a production app. Rails makes 100 SELECTs every frikking time the page gets loaded. EVERY TIME. Then, after getting all those objects, it puts it into a list and does an array.size ... OMFG!!!

anyhoo, this is what I did, put the sql in a script and have it run every 15 minutes or so. Why? because it is a general sense that the users need. Not an exact number.

-- set the total ads per "SUBCATEGORY" in the categories table

begin transaction;

update categories set ads_count=(select count(*) from ads a where a.category_id = categories.id);

commit;

-- Get totals of the ad counts for each main category ("topdogs")
select name, ads_count , (select sum(ads_count) from categories b where b.parent_id = a.id) as totcounts from categories a where a.parent_id is null;

-- Now set the total values for the main cats by summing the ad counts for all of their
-- subcategories.

begin transaction;

update categories
set ads_count=(select sum(ads_count) from categories b where b.parent_id = categories.id)
where categories.parent_id is NULL;

commit;

just put it in a rake task and have cron run it periodically, and comment out all that 100 select per page nonsense which is not necessary.

adios!