Database hierarchy filtering

[eluser]Triple_vent[/eluser]
i guess this is just basic question, sorry for people who check my question and got a little smile (since i have no others people to ask)

i point here because i am currently creating my first application with CI, its already 60% finish but i got some problem when i am aware that i stuck on database issues....


1. to make it clear my database design is like this (this just basic) :


----------- ------------ ------------
category asset location
------------ ------------ ------------
|id (primary) |id (primary) |id (primary)
|name |idCategory |id asset
|parent_id |name |name
| |descr |descr

parent_id is used when there is a subcategory for asset

2.how to filter (with sql query) from category to search which asset located on certain placed (based on location id) but return
in my php-view page it will become like this :
category name
-subcategory name
-- asset name (which is located on some placed as filtered)

[eluser]Triple_vent[/eluser]
if some poeple here can give any advice about The model deal with my problem (Just the model, if you dont mind), i am so very grateful or you got no time to answer mine, could anyone give me some reference..for it... thanks

[eluser]wabu[/eluser]
If I were you I'd break the query up into its component parts, fire up whatever SQL client you prefer (command line or GUI) and get each part of the query working, adding a little more each time until you have the result set you want.

To me it sounds like:

- given a particular category id and location id (both, if I read your post correctly)
- get all related assets
- display parent category information if it's a subcategory

So if it's giving you trouble don't try to get everything working at once, just get each part working (for example start by getting assets for a category without considering location or joining/subquerying for parent categories) and then build it up piece by piece.

Then once it's all working go back to CI and see if you can translate your query to Active Record (if that's your preference).

Take small bites.

[eluser]Triple_vent[/eluser]
[quote author="wabu" date="1250448431"]If I were you I'd break the query up into its component parts, fire up whatever SQL client you prefer (command line or GUI) and get each part of the query working, adding a little more each time until you have the result set you want.

To me it sounds like:

- given a particular category id and location id (both, if I read your post correctly)
- get all related assets
- display parent category information if it's a subcategory

So if it's giving you trouble don't try to get everything working at once, just get each part working (for example start by getting assets for a category without considering location or joining/subquerying for parent categories) and then build it up piece by piece.


[/quote]

thanks for your reply, you right i assume i provide $_POST['category'] and $_POST['id_location'] in my gui php view, and when user click search the result is what i explain on my first post.

to break the sql piece by piece i agree, but i takes 2 more days or more for unexperienced person like me to make it work..

Then once it's all working go back to CI and see if you can translate your query to Active Record (if that's your preference).

yeah iam using active record for all my 60% CI database...(the sql is just an option, if people provide the model based on that rather than Active record).

honestly i am just wondering if there are some people who already done it so it take little time for people like me to speed up my application to be done...