With

Sometimes we forgot the n+1 problem in developing and fetching datas in our application. This problem can cause tremendous amount of speed/memory consumption and creates a low performance application. Sprnva solve this problem using with method.

When using the with method this will add all the data of the foreign key to the result of the selected table.

Using this method is like saying as: "get this selected table "with" all the data of it's selected foreign key"

with([
    'relational-table' => [
        'foreign-key-in-the-selected-table',
        'primary-key-column-of-the-relational-table'
    ]
])

/**
 * In our example below the database look like this
 * 
 * projects
 *  - id
 *  - user_id
 *  - roles_id
 * 
 * 
 * users
 *  - id
 * 
 * 
 * roles
 *  - id
 * 
*/

// when using the with method
Route::get('/with', function () {

    // the selected table which is "projects"
    $projects = DB()->selectLoop("*","projects", "id > 0")
        ->with([
            // relation-table "users"
            "users" => [
                // foreign-key-in-the-selected-table
                'user_id', 

                // primary-key-column-of-the-relational-table
                'id'
            ],

            // relation-table "roles"
            "roles" => [
                // foreign-key-in-the-selected-table
                'roles_id',

                // primary-key-column-of-the-relational-table
                'id'
            ]
        ])->get();

    // this is how we retrieve the result of our query
    foreach($projects as $project){

        echo "Project Name: " . $project['project_name'];
        echo "<br>";

        // this is how we retrieve the data of the relation table
        echo "Assigned Person : ". $project['users']['fullname'];

        echo "<br>";
        echo "The Role : ". $project['roles']['role'];

    };

});

Take note when retrieving the relational table datas use the relation-table that you specify in the with method followed by the column name you want to retrieve from the relation-table like this : $project['users']['fullname']

This is an example how to use with and get all the values of the foreign id.

WHERE :

• projects is the selected table

• users is the relational-table

• roles is the relational-table

• user_id is the foreign-id-in-the-selected-table

• roles_id is the foreign-id-in-the-selected-

• id is the primary-key-column-of-the-relational-table

Behind the scenes, first the with() method will get all the selected table datas and it makes our 1st query. Then it collects the foreign-id-in-the-selected-table and then fetch all the data from the users relational-table and this makes our 2nd query and fetch all the data from the roles relational-table and this makes our 3rd query. Finally it combines the relational-table datas to the corresponding iteration of the projects loop.

This also applies and tested on select() method same process same logic and the result is the same.

This way we avoid the querying data to our database inside our loop and makes our query repeats until the end of the iteration. Put in mind that if you have a 100,000 items in our table and we iterate all of it then we query inside the loop to get the foreign key data, imagine the pain that our server gets. Cheer up! sprnva got this under the hood.