> For the complete documentation index, see [llms.txt](https://jls-organization-3.gitbook.io/projecthyper/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://jls-organization-3.gitbook.io/projecthyper/features/quickstart/relationships.md).

# Relationships

Relationships are the connections between different tables in a database. These relationships allow you to efficiently store and retrieve related data. PHP is often used to interact with MySQL databases and manage these relationships.

**Common Types of Relationships**

1. **One-to-One:**
   * A single record in one table corresponds to exactly one record in another table.
   * **Example:** A `users` table and a `profile` table, where each user has one profile.
2. **One-to-Many:**
   * A single record in one table can be associated with multiple records in another table.
   * **Example:** An `authors` table and a `books` table, where one author can write many books.
3. **Many-to-Many:**
   * Multiple records in one table can be associated with multiple records in another table.
   * **Example:** A `students` table and a `courses` table, where one student can take many courses, and one course can be taken by many students.

**Example:**

For example, we have 3 tables

<table><thead><tr><th width="264">Table</th><th>Fields</th></tr></thead><tbody><tr><td>news</td><td>id, name, status</td></tr><tr><td>news_details</td><td>id, news_id, content</td></tr><tr><td>news_attachments</td><td>id, news_details_id, url</td></tr></tbody></table>

Each table is referenced to another table where news -> news\_details -> news\_attachments.

**Usage:**

<pre class="language-php"><code class="lang-php"><strong>$news = $db->news->findOne(1);
</strong>// This only returns
{
    "id": "1",
    "name": "Test News",
    "status": "ACTIVE",
    "created_at": "2024-11-03 21:07:37",
    "updated_at": null,
    "deleted_at": null
}
</code></pre>

However, if we transform the options into

```php
$news = $db->news->findOne(
    array(
        'where' => array('id' => 1),
        'include' => array('news_details' => true)
    )
);
// This will return the news and the news_details
{
    "id": "1",
    "name": "Test News",
    "status": "ACTIVE",
    "created_at": "2024-11-03 21:07:37",
    "updated_at": null,
    "deleted_at": null,
    "news_details": [....]
}
```

What if the requirement is to return only a single record? Just pass `single` attribute to the object

```php
$news = $db->news->findOne(
    array(
        'where' => array('id' => 1),
        'include' => array(
            'news_details' => array(
                'single' => true // ADD THIS
            )
        )
    )
);
// This will return the news and the news_details (1 row)
{
    "id": "1",
    "name": "Test News",
    "status": "ACTIVE",
    "created_at": "2024-11-03 21:07:37",
    "updated_at": null,
    "deleted_at": null,
    "news_details": {
        "id": "1",
        "news_id": "1",
        "content": "Test",
        "created_at": "2024-11-03 21:07:37",
        "updated_at": null,
        "deleted_at": null,
    }
}
```

{% hint style="info" %}
The include option can also be added inside the relation to create a hierarchy of data
{% endhint %}

```php
$news = $db->news->findOne(
    array(
        'where' => array('id' => 1),
        'include' => array(
            'news_details' => array(
                'single' => true,
                'include' => array(
                    'news_attachments' => true
                )
            )
        )
    )
);
// This will return the news and the news_details (1 row) and attachments
{
    "id": "1",
    "name": "Test News",
    "status": "ACTIVE",
    "created_at": "2024-11-03 21:07:37",
    "updated_at": null,
    "deleted_at": null,
    "news_details": {
        "id": "1",
        "news_id": "1",
        "content": "Test",
        "created_at": "2024-11-03 21:07:37",
        "updated_at": null,
        "deleted_at": null,
        "news_attachments": [...]
    }
}
```

The `include` method is flexible as well to the `where`, `order`, `group` , `limit`, `offset` etc.

<pre class="language-php"><code class="lang-php"><strong>$condition = array(
</strong>        'where' => array('id' => 1),
        'include' => array(
            'news_details' => array(
                'single' => true,
                'where' => array(
                    'created_at' => array(
                        'between' => array('2024-01-01', '2025-01-01')
                    )    
                ),
                'include' => array(
                    'news_attachments' => array(
                        'order' => array(
                            'created_at' => 'DESC'
                        ),
                        'where' => array(
                            'url' => array('not_null' => true)
                        ),
                        'limit' => 10,
                        'offset' => 0
                    ),
                )
            )
        )
    );
    
$news = $db->news->find($condition);
</code></pre>