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	Fields
news	id, name, status
news_details	id, news_id, content
news_attachments	id, news_details_id, url

Each table is referenced to another table where news -> news_details -> news_attachments.

Usage:

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

However, if we transform the options into

$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

$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,
    }
}

The include option can also be added inside the relation to create a hierarchy of data

$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.

$condition = array(
        '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);