jeudi 17 décembre 2020

MySQL join or call multiple instantiation methods to run queries in PHP MVC?

I've been building a webapp using MVC design patterns in PHP recently, and I'm undecided over two different ways of getting the same data as I'm not sure which would be better practice / more efficient.

Let's say I am building an app for sorting films and I have two models, one for films and one for genres, and I want to get all the films (including genres) with a certificate of 15 (or your national equivalent).

So this is what I think is the more efficent route to get all of the data from the DB, by using a join to get it all at once:

class Film {
    private $db;
    private $title;
    private $genre;

    function __construct($db, $title, $genre) {
        $this->db = $db;
        $this->title = $title;
        $this->genre = $genre;
    }

    static function getFilmsByCert($db, $cert) {
        $records = $this->db->query('SELECT f.title, g.name FROM films f INNER JOIN genres g ON f.gid = g.id WHERE f.cert = ?', array($cert));
        
        $filmList = array();
        foreach($records as $filmData) {
            $genre = new Genre($db, $filmData['g.name']);
            $filmList[] = new Film($db, $filmData['f.title'], $genre);
        }

        return $filmList;
    }
}

class Genre {
    private $db;
    private $name;

    function __construct($db, $name) {
        $this->db = $db;
        $this->name = $name;
    }
}

I would then call Film::getFilmsByCert($db, '15');.

However, I am aware that a more 'correct' approach in MVC would probably be to use the Genre model to get all of genre data.

So we would add the following to the Genre model:

static function getById($db, $gid) {
    $name = $this->db->getval('SELECT name FROM genres WHERE id = ?', array($gid));
    return new Genre($db, $name);
}

and getFilmsByCert() would instead be:

static function getFilmsByCert($db, $cert) {
    $records = $this->db->query('SELECT title, gid FROM films WHERE cert = ?', array($cert));
    
    $filmList = array();
    foreach($records as $filmData) {
        $genre = Genre::getById($db, $filmData['gid']);
        $filmList[] = new Film($db, $filmData['f.title'], $genre);
    }

    return $filmList;
}

Now this more readable and cleaner, but surely when scaled up to many, many different models and properties, this would be much slower as it running more database queries as opposed to the single one in the first example?

Also I am sure the pendants will want to let me know these are actually domain objects not models but from what I've read everyone seems to have a slightly different idea of how to implement MVC anyway.

Aucun commentaire:

Enregistrer un commentaire