• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Models & Relational Tables

#1
I have 3 database tables:
Code:
CREATE TABLE booking
(
   booking_id INT(9) unsigned NOT NULL AUTO_INCREMENT,
   deleted TINYINT(1) DEFAULT '0' NOT NULL,
   created_date_time DATETIME NOT NULL,
   updated_date_time DATETIME,
   CONSTRAINT `PRIMARY` PRIMARY KEY (booking_id)
);

CREATE TABLE booking_ticket
(
   booking_ticket_id INT(9) unsigned NOT NULL AUTO_INCREMENT,
   booking_id INT(9) unsigned NOT NULL,
   ticket_id INT(9) unsigned NOT NULL,
   CONSTRAINT `PRIMARY` PRIMARY KEY (booking_ticket_id, booking_id, ticket_id)
);

CREATE TABLE ticket
(
   ticket_id INT(9) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   ticket_number VARCHAR(255) NOT NULL,
   deleted TINYINT(1) DEFAULT '0' NOT NULL,
   created_date_time DATETIME NOT NULL,
   updated_date_time DATETIME
);

CREATE UNIQUE INDEX ticket_number ON ticket (ticket_number);

But I only want 2 models:
PHP Code:
class Booking extends Model
{
}

class 
Ticket extends Model
{


And 2 domain objects:
PHP Code:
class Booking
{
   public $bookingId;
   public $tickets = [];
}

class 
Ticket
{
   public $ticketId;
   public $ticketNumber;
   public $booking;


How should I handle the `booking_ticket relational table and the domain objects `Booking->tickets` and `Ticket->booking`?

I've got a half-baked solution that I'm really not happy with and would like to see what others solutions are?
Kristian Matthews
Apple Certified Associate Mac Integration & Management 10.10
epickris.com
Reply

#2
While it's not 100% pretty, I'll add new methods to the model for each situation, something like:

Code:
class Booking extends Model {
    public function getTicketsForBooking(int $bookingID)
    {
        return $this->db->table('tickets')->join('booking_ticket', 'ticket_id = tickets.id')->where('booking_id', $bookingID)->get();
    }
}

Or something similar. There's usually some sanity checks in there to make sure that $bookingID is a valid booking, or it throws an exception, etc.

it's not super elegant, but it is straight-forward, easy to follow and understand. Nowadays I'll typically have an actual Repository-type class that holds an instance of the model, and has custom methods for most of the situations I need, which is why it's written like it is, but I've done it straight inside the model class itself many times.
Practical CodeIgniter 3  • CodeIgniter 4 Foundations - Coming Soon • Vulcan - CLI Tools for CI4
Reply

#3
(03-13-2017, 02:11 PM)kilishan Wrote: While it's not 100% pretty, I'll add new methods to the model for each situation, something like:

Code:
class Booking extends Model {
   public function getTicketsForBooking(int $bookingID)
   {
       return $this->db->table('tickets')->join('booking_ticket', 'ticket_id = tickets.id')->where('booking_id', $bookingID)->get();
   }
}

Or something similar. There's usually some sanity checks in there to make sure that $bookingID is a valid booking, or it throws an exception, etc.

it's not super elegant, but it is straight-forward, easy to follow and understand. Nowadays I'll typically have an actual Repository-type class that holds an instance of the model, and has custom methods for most of the situations I need, which is why it's written like it is, but I've done it straight inside the model class itself many times.

Could you explain more about your repository-type classes?

To use my domain models in my models I've created a basic data mapper (to map arrays to objects) and extended the core model's class to array method.
Kristian Matthews
Apple Certified Associate Mac Integration & Management 10.10
epickris.com
Reply

#4
(03-13-2017, 03:35 PM)EpicKris Wrote: Could you explain more about your repository-type classes?

Sure. Basically, I create a new class to act as a Repository and is a layer that separates me from the model. That way if I ever need to change the model, or the type of database it's using, etc, I can do that at the Repository level and not impact the rest of the app. With a fluent interface it might look something like:

Code:
use App\Models\BookingModel;
use App\Models\TicketModel;
use CodeIgniter\Database\ConnectionInterface;

class BookingRepository {
    
    protected $bookings;
    protected $tickets;
    protected $db;

    public function __construct(BookingModel $bookings, TicketModel $tickets, ConnectionInterface $db)
    {
        $this->bookings = $bookings;
        $this->tickets  = $tickets;
        $this->db       = $db;
    }

    // Basic CRUD functions for a Fluent interface to create/update the objects.
    public function find(int $id) { }
    public function findMany(array $ids) { }
    public function findAll(int $perPage=0) { }
    public function setUserID(int $id) { }
    public function setTickets(array $tickets) { }
    public function withTicket(Ticket $ticket) { }
    public function create();
    public function update(int $bookingID, array $params=[]);
    public function delete(int $bookingID);

    // Custom, task-specific methods...
    public function getTicketsForBooking(int $bookingID)
    {
        return $this->tickets->where('booking_id', $bookingID)->get();
    }

    public function addTicketToBooking(Ticket $ticket, int $bookingID)
    {
        $db = Config\Database::connect();

        return $db->table('bookings_tickets')->insert([
            'booking_id' => $bookingID,
            'ticket_id' => $ticketID
        ]);
    }

    public function cancelBooking(int $bookingID)
    {
        ...
    }
}

The other thing nice is that this allows method names that read well in the controllers. Additionally, this can be much easier to test in many cases.

In CI4, I'd create a service for this in Config\Services.php

Code:
public static function BookingRepo()
{
    return new App\Repositories\BookingRepository(
        new App\Models\BookingModel(),
        new App\Models\TicketModel(),
        Config\Database::connect()
    );    
}


Hope that makes sense. It's by no means the only way to do it, but even at the current company I work at, which is a Laravel shop, we do something very similar, ignoring much of the relationship magic in Eloquent.
Practical CodeIgniter 3  • CodeIgniter 4 Foundations - Coming Soon • Vulcan - CLI Tools for CI4
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.