Entities and relationship

Greetings people,


I'm new to Code Igniter 4 ans after browsing your documentation, I have a question that hasn't found its answer yet. I would like to know what is the proper way to deal with foreign keys when we are working with entities.

Let's take an example: I have a table for books, and a table for authors. Obviously, the books table will have a foreign key toward the author table.
If I ask my model to perform a $bookModel->find(1) I will get the book id #1 from the table, and the ->getAuthor() method will get me an ID, like let's say 9

Now, I don't much care about this ID. What I really want is the name of the author, which is set in the "name" column of the author table.
How do you properly get that? Do I really have to send another query, like "authorModel->find(9)" ? that would be very very heavy in the end, in term of sql queries amount....
Or is there another way more classy option?

note: I know there are ORMs working with CI4 which would solve this problem. But due to the structure of the database I currently have to work with, none of them would work (I am using mysql, and all the data are split between multiple bases - as in sql "CREATE DATABASE"- , with tons of relationship between them. While all ORMs I have found requires to connect specifically to only one of them)

Do I really have to send another query, like "authorModel->find(9)" ? that would be very very heavy in the end, in term of sql queries amount....

Not that much. Don't underestimate the speed a database read data. Getting a single row with its primary key is extremely fast. Making a single select that joined multiple tables won't be necessarily faster than making multiple select of single rows with its id.

As you mention, ORMs can handle this for you. You might also check out my package, Tatter\Relations, that automates this - you might run into some of the same issues spanning databases but it should at least give you an idea for what sort of methods you can add to your Entities to facilitate loading related items.

Personally, I like to use Entities to help with this. When there is a foreign key referencing another database table, I use SQL joins to pull in foreign data as part of a single SELECT statement.  Then my model instantiates an Entity object where the foreign data, a "child entity", is an attribute of the "parent entity".  This prevents excess database calls and allows me to use all of the "child entity" methods within the "parent entity".

For example, say you have a table of companies and table of employees and you want to select a particular employee with all associated company data.  This is pretty simple to do with an SQL join.  By default, CodeIgniter would typically dump the employee and company data into a single array (or even a single Entity) and you could access it from there.  I would create two Entity classes. In this case, Company and Employee.  In you model, you would need to create a method that would  instantiate an Employee Entity object with the data from the employee table only.  Then instantiate a Company Entity object with the data from the company table as a property of the Employee Entity.

AFAIK, CodeIgniter Entities don't handle inheritance very easily. This is a kind of workaround.

mlurie

Can you give some code example. Its interesting.
Thanks

Thank you @MGatner for your work.
I'm trying to use it but i get "Call to undefined method App\Models\TaskModel::with" in controller at line
$tasks = $this->taskModel->with('tags')->findAll();

I understand TaskModel does not extend the right model because i have coded :
use CodeIgniter\Model;
AND
use \Tatter\Relations\Traits\ModelTrait;
BUT :
class TaskModel extends Model

How should i code it to extend your "Tatter\Model" ?

Tables involved are tasks, tags and tasks_tags

Help would be highly appreciated

Thanks

Hey there Ignited fellows'!

As of my understanding each CodeIgniter model should essentially have one corresponding Entity if any. 
And in your model you should define it in:

PHP Code:

$this->returnType = 'App\Entity\Book'; 


Further more each database table should have its corresponding Model
in which you describe that it is connected to that table and fill it up with necessary logic / methods.

PHP Code:

$this->table= 'books'; 


All above goes for the authors table, model and entity aswell.

Now when your books have authors then of course you might want to fetch the author data.
Say you have fetched a book by it's id.

PHP Code:

$entity = $this->find(1); 


In order to preserve books entity key correlation to its table keys we should not pollute books
entity with authors data, instead, we're going to make a method to retrieve our related author on demand:

PHP Code:

<?php
namespace App\Entity\Book;

# Author is a sub-entity of any book.
use App\Models;

class Book
{
    # ...
    public function getAuthor() : null|Models\Author
    {
        # Check if current entity has been loaded.
        if ($this->author_id)
        {
            # Assuming you have a model Author with constructor
            # that's taking in first argument as int|object to instantiate local entity.
            return new Models\Author($this->author_id);

            # Or if you go very CI native you could instead do it like this:
            $author = new Models\Author();
            $author->entity = $author->find($this->author_id);

            # Either way we're returning current entity's submodule.
            return $author;
        }

        # Nothing found.
        return null;
    }
    # ...
} 


In reality when you are fetching ~10,000 books which in total maybe have 700 authors then
it is very bad practice to instantiate entirely new model for each and every author.

This is something I have talked about before on this forum. What I have done to avoid
it on my end (in my projects) is a global helper similar to service() but instead of sharing the
very same instance globally trough out the project I create a table in memory which is keyed
by given arguments and when same class with same construction arguments is requested
it will actually return a reference to already created class within memory. But that's another story.

Now say in the book model you would like to access its authors data? You do it like this:

PHP Code:

$author_entity = $entity->getAuthor()->entity; 


And the benefit of having Entities to children entire models is access to their methods:

PHP Code:

# I'm currently programming Books model and here's the logic where
# I would like to remove the author of this book for ever.
$entity->getAuthor()->delete(); 


In reality when you are fetching ~10,000 books which in total maybe have 700 authors then
it is very bad practice to instantiate entirely new model for each and every author.

Using Entities to look up related items is a great idea. I have been doing this with great success since I started using CI4, but I have actually fallen trap to the concept that stopz is suggesting against, wherein I end up loading instances of models for every lookup, and this is definitely not good practice, I would agree.

So I started thinking about how I could do this better, and I have a suggestion to throw out there. I use static classes a LOT for my own libraries. I find static classes to be a great tool for having methods at the ready at any time without instantiation, and also as a means of "building" a class during a routine, adding things to it, and having it almost like a scratchpad or a way of keeping things in memory as the procedure continues.

Therefore, one suggestion I can offer is to use a static class to load your models, keep them in the class, and then reuse the same loaded model rather than reloading over and over again.

PHP Code:

<?php

namespace App\Libraries;

class ModelLoader
{
    public static $models = [];

    public static function fetch($model_name)
    {
        if (!isset($models[$model_name])) {
            $formatted_model_name = '\\App\\Models\\' . $model_name;
            $model = new $formatted_model_name();
            self::$models[$model_name] = $model;
        }

        return self::$models[$model_name];
    }
} 


This is my library. Imagine that I have a table called 'applications' and another one called 'tokens' that has an application_id field in it. I can load the related token within the Entity and then fetch it simply by loading the $application first and then using $application->token. I can load TONS of applications, and fetch TONS of tokens, but only load the model once. No matter what the solution, using Entities for ORM is really the best solution I've seen so far.

PHP Code:

<?php

namespace App\Entities;

use CodeIgniter\Entity\Entity;
use App\Libraries\ModelLoader;

class Application extends Entity
{
    public function getToken()
    {
        $model = ModelLoader::fetch('Tokens');
        return $model->find($this->attributes['application_id']);
    }
} 


Hi,

@viewfromthenorth but solves this the problem that you make tons of queries? If you fetch 1000 applications you make 1000 more queries to get the token?

@viewfromthenorth If I'm understanding your post correctly, CodeIgniter's model() helper method already does this for you.

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials