[FIXED] Join Table with the id of another join table

Issue

i'm currently trying to build a backend for a project. In this project you will be able to create "ContentElements" that can be used to display content in a page (in my case Sites). Every ContentElement can have multiple options. When a user creates a new Site with an ContentElement (e.g. header) he would enter all options of the element. For example:

"src": "/img/bg.jpg",
"text": "Lorem ipsum..."

In order to save the option's value per page it is used in, i store these values in a separate table (content_elements_sites_values).

My scheme currently looks like this: data scheme

So what i'm currently trying to do is when i get all data associated with the Site i also want to get the data from 'content_elements_sites_values'

$site = $this->Sites->get($id, [
        'contain' => ['Templates', 'Colors', 'SiteTypes', 'ContentElements' => [
            'ContentElementOptions' => [
                'ContentElementsSitesValues'
                ]
            ]
        ],
        'conditions' => [
            // Just to explain my problem.
            'ContentElementsSites.id' => 'ContentElementsSitesValues.content_elements_sites_id'
        ]
    ]);

I really don't know if this is even possible or even if my "design" is a total bull***t. But i cannot think of another way to store the filled in data. I'm very open to suggestions of a better way to store this data. Please ask if you need further information in order to help me.

Thank you in advance!

---

EDIT

I try to explain better what i want to achieve.

Every site can have multiple content_elements of the same type (association is stored in content_elements_sites junction table).
Every content_element can have multiple content_element_options

All content_element_options and content_elements are defined by an Admin.

Another user can create a site and populate it with content_elements and enter content_elements_sites_value for all content_element_options. And as the same content_element (e.g. a paragraph or a list) can have multiple occurrences in the same site, i'll need to store every content_elements_sites_value the user entered.

Thats why i created the link between content_elements_sites and content_element_options.

Currently i'm using this query to get everything expect the value:

$site = $this->Sites->find('all', [
        'conditions' => [
            'Sites.id' => $id
        ],
        'contain' => ['ContentElements' => [
                'sort' => [
                    'ContentElementsSites.order' => 'ASC'
                ],
                'ContentElementOptions' => [
                    'ContentElementsSitesValues' => [
                        'conditions' => [
                            'ContentElementsSitesValues.content_elements_sites_id' => 'ContentElementsSites.id',
                        ]
                    ]
                ]
            ]
        ]
    ]);

This results in empty content_elements_sites_values

(int) 1 => object(App\Model\Entity\ContentElementOption) {
    'id' => (int) 7,
    'content_element_id' => (int) 1,
    'name' => 'Test',
    'json_name' => 'test',
    'option_type_id' => (int) 1,
    'content_elements_sites_value' => null,             
}

My scheme currently looks like this: data scheme

I'm wondering if this query is even possible. Or if the whole thing is just too flexible.

Solution

The way you have defined the relationships signifies that you wish to have a very modular approach so that a content element can be used with multiple sites and a content element option can be used with multiple content elements.

If that is the case, schema direction looks okay with few changes :

1) content_elements_sites_values table can have site_id column directly instead of content_elements_sites_id column as site will be always unique for an entry in that table so the connection id of content_elements_sites isn't required.

2) content_elements_sites_values table can be renamed to content_element_options_values.

3) You can remove id column from content_elements_sites and content_elements_sites_values junction tables.

Answered By - Gaurav