Welcome Guest, Not a member yet? Register   Sign In
DB Using CodeIgniter’s Model - $afterFind
#1

Hi everyone, I’m doing my own project and I have a couple of tables and I want to make a connection between them through $afterFind, but in the documentation, it’s not clear how to do it correctly. I have a table of films and there are additional tables for films (genres, countries, actors) and I need to link them to the main table of films and set up specific output fields. Maybe someone already made a similar connection.
Reply
#2

(06-18-2020, 11:47 AM)midav Wrote: Hi everyone, I’m doing my own project and I have a couple of tables and I want to make a connection between them through $afterFind, but in the documentation, it’s not clear how to do it correctly. I have a table of films and there are additional tables for films (genres, countries, actors) and I need to link them to the main table of films and set up specific output fields. Maybe someone already made a similar connection.

I also have a table of films, but I thought a different structure than yours. It was developed at CI3 and recently updated to CI4.
There are 3 tables: the one of films, of names - actors, directors, screenwriters - and one where I make the relationship between films and names.
It's a hobby for me, I'm not a professional, and it's just local, everything based on the IMDb website, including the layout, design, in my opinion the most difficult and complicated part.
When I started to develop I didn't know anything, so there are certainly a lot of structural errors that don't compromise the result.
The main table, "movies", includes all fields with information about each film except actors, the names that appear are highlights, and reference to awards received, restricted to Oscar, Golden Globe and BAFTA.

It is important to note that a migration was made from CI3 to CI4, the development was not specific to CI4.

Below, the "movies" controller with the "movie" and "awards" methods:
Code:
<?php namespace App\Controllers;
   
    // 20200508
   
    use CodeIgniter\Controller;
    use App\Models\MovieModel;
    use App\Models\AwardsModel;
    use App\Models\OscarModel;
    use App\Models\GlobeModel;
    use App\Models\BaftaModel;

    class Movies extends Controller {

    public function movie() {

        $model = new MovieModel();

        $data = [
            'result'    => $model->getMovie(),
            'stars'    => $model->getStars(),
            'runtime'  => $model->getRuntime(),
            'plustime'  => $model->getPlusTime(),
            'release'  => $model->getRelease(),
            'dir1'      => $model->getDir1(),           
            'dir2'      => $model->getDir2(),
            'dir3'      => $model->getDir3(),
            'dirx1'    => $model->getDirx1(),
            'dirx2'    => $model->getDirx2(),
            'dirx3'    => $model->getDirx3(),
            'wtr1'      => $model->getWtr1(),           
            'wtr2'      => $model->getWtr2(),
            'wtr3'      => $model->getWtr3(),
            'wtrx1'    => $model->getWtrx1(),
            'wtrx2'    => $model->getWtrx2(),
            'wtrx3'    => $model->getWtrx3(),
            'cast'      => $model->getCast(),
            'star1'    => $model->getStar1(),
            'star2'    => $model->getStar2(),
            'star3'    => $model->getStar3(),
        ];

        foreach ( $model->getMovie() as $row );

        $header = array (
            'icon' => 'favicon',
            'css' => 'movie',           
            'title' => $row->title.'&nbsp('.$row->ano.') | Créditos | wdeda',
            'action' => '/search/allmedia/',
            'placeholder' => 'Pesquisar'
        );

        echo view('templates/header', $header);
        echo view('content/movies/movie', $data);
        echo view('templates/footer');
    }

    public function awards() {

        $model1 = new MovieModel();
        $model2 = new AwardsModel();
        $model3 = new OscarModel;
        $model4 = new GlobeModel();
        $model5 = new BaftaModel;

        $data = [
            'award'    => $model2->getAwards(),
            'oscprz'    => $model3->oscprz(),
            'oscnom'    => $model3->oscnom(),
            'glbprz'    => $model4->glbprz(),
            'glbnom'    => $model4->glbnom(),
            'bftprz'    => $model5->bftprz(),
            'bftnom'    => $model5->bftnom()
        ];

        foreach ( $model1->getMovie() as $row);

        $header = array (
            'icon' => 'favicon',
            'css' => 'awards',
            'title' => $row->title.' | Premiações | wdeda',
            'action' => '/search/allmedia/',
            'placeholder' => 'Pesquisar'
        );

        echo view('templates/header', $header);
        echo view('content/movies/awards', $data);
        echo view('templates/footer');


    }
 

}


The model is extensive, 777 lines, so I'll just show the function where the associated names of the artists_movies table are taken:

Code:
public function getStars() {
        $request = \Config\Services::request();
        $uri = $request->uri;
        $id = $uri->getSegment(3);
        $db = db_connect();
        $sql = ("SELECT names.*, artists_movies.* FROM names, artists_movies
        WHERE artists_movies.movie_id=$id
        AND
        artists_movies.artist_id=names.id
        ORDER BY position, id ASC");
        $query = $db->query($sql);
        return $query->getResult();
    }

The homepage with prominence for films, there is also a catalog for music, records; the moviepage and awardspage:

Attached Files Thumbnail(s)
           
Reply
#3

(This post was last modified: 06-18-2020, 10:10 PM by bivanbi.)

There are two approaches I use for multi-table operation, depending on what I want to achieve.


1) Database Views or JOIN statements: for read-only operation to get flat data (rows) with a single select
For instance, to get a list of films that have been given an academy award with all the details from both Film and Awards tables.
In this case you do not need afterFind(), because you get all the data in a single SELECT statement.

2) Higher Order Entity + one base model and entity per DB table: to read AND write hierarchical data
For instance, to get a complete record of a movie with the directors, actors, awards, links to trailers, reviews etc.
I guess this is where you could use afterFind() method in your MovieModel so that when the movie is found, you can take care of loading all the subordinate data like actors in your afterFind() method:

Code:
class MovieModel extends Model {
    (...)
    protected function afterFind(array $data)
    {
        $data['data']['actors'] = $this->actorModel->where('movie_id', $data['id'])->findAll();
        $data['data']['awards'] = $this->awardModel->where('movie_id', $data['id'])->findAll();
        (...)
        return $data;
    }  
     
}


My personal opinion on handling structured data in a flat database like MySQL is this. Since Entities and Models are - in my opinion - meant to work on flat data (single table row) and a single database table (or view, that is), extending them to work well with hierarchical data would result in a mess.

So I decided to create a totally independent set of classes to handle the situation. This I call DataStructure and HigherOrderDataStructure. Basically, a DataStructure holds an entity and a model for a single database table, while HigherOrderDataStructure holds the 'top level' entity and model – like the Movie in our example – and an arbitrary number of 'subordinate' DataStructures like Actor. Those supordinates can also be HigherOrderDataStructures – like Actors can have multiple Awards – to achieve multiple levels of hierarchy like in an XML or JSON file. Since HigherOrderDataStructure is designed from de ground up to work with subordinates, it can inherently handle the afterFind() situation itself. Also I can hide the entity and model classes from the rest of the application. I can think of DataStructure as an adapter to CI4's Entities and Models – in my DataStructure I could work around a CI4 bug in Model->save() method resulting in exception when I try to save an unchanged entity into database table. Big Grin
Reply
#4

(This post was last modified: 06-18-2020, 11:45 PM by midav.)

I only need reading, and I thought you can do it all through $afterFind

Like it now.
http://prntscr.com/t2isen

And get the following result
http://prntscr.com/t2its4

as I understand it, I need to write my processing function and call it so protected $afterFind = ['filmsCat'];
Reply
#5

(06-18-2020, 11:45 PM)midav Wrote: I only need reading, and I thought you can do it all through $afterFind

as I understand it, I need to write my processing function and call it so protected $afterFind = ['filmsCat'];

Yes, you are correct: with $afterFind = .. you define which method in your own model class the parent Model will call after data has been loaded from database. This method is totally yours to make, so here you can load any additional data you see fit. It will receive the original data loaded from DB as an argument. Just make note that the $data array you receive is built like this:
PHP Code:
$data = [
       'id' => <primary key of database row>,
       'data' => [
           // actual row data: key-value pairs
       ],
]; 

Please verify the above with var_dump or similar in your protected function filmsCat(array $data).
Reply
#6

(This post was last modified: 06-19-2020, 07:08 AM by midav.)

http://prntscr.com/t2rr7y

as for me this is also a solution but I need an optimized query using join, maybe someone will advise how to do it even more correctly. Because I could write a join function but it can somehow be implemented in a function that I wrote because it turns out that as many as 52 queries to the database are not good.

the result is what I need

http://prntscr.com/t2ruv4

I am only bothered by the number of requests of them as many as 50 pieces because I still use pagination and there I have to display 25 entries per page

but I would like that I created a model somehow add join so that I would not have to write additional functions in the model.

http://prntscr.com/t2s012

the screenshot shows that it also works quickly, but if I have a very large base, then I think I need to think something with optimization, and now I want to immediately lay down all the queries normally with a bunch of several tables.

http://prntscr.com/t2s5y8

and so I want to call in the controller
Reply




Theme © iAndrew 2016 - Forum software by © MyBB