Database Structure for CakePHP Models

Question

We're building a data tracking web app using CakePHP, and I'm having some issues getting the database structure right. We have Companies that haveMany Sites. Sites haveMany DataSamples. Tags haveAndBelongToMany Sites.

That is all set up fine. The problem is "ranking" the sites within tags. We need to store it in the database as an archive. I created a Rank model that is setup like this:

rank (
    id (int),
    sample_id (int),
    tag_id (int),
    site_id (int),
    rank (int),
    total_rows)
)

So, the question is, how do I create the associations for tag, site and sample to rank? I originally set them as haveMany. But the returned structures don't get me where I'd like to be.

It looks like:

[Site] => Array (
    [Sample] = Array(),
    [Tag] = Array()
)

When I'm really looking for:

[Site] => Array (
[Tag] = Array (
    [Sample] => Array (
        [Rank] => Array (
            ...data...
        )
    )
)
)

I think that I may not be structuring the database properly; so if I need to update please let me know. Otherwise, how do I write a find query that gets me where I need to be? Thanks!

Thoughts? Need more details? Just ask!

Answer 1

To get that format ...

Tag hasOne Sample Sample hasOne or hasMany Rank

The alternative is to "model hack" as i call it, which is basically creating custom methods with the model class, that exec custom queries and format the results how you desire.

Answer 2

According to the data format you want,I suggest you changing sites haveMany samples to tags hasMany samples.And make sure you have bulit the table which is

samples_tags (
id (int),
sample_id (int),
tag_id (int)
)

And also check you have defined the models' association correctly.More detail please see HABTM in cookbook.