Create ORM to solve Models and Joins

[harakiri1234]

Hey guys,

My task is to build ORM without any framework. So far, I have fetched everything inside ProductModel.php, but some data are joined (example down below), and they come from 2 different tables. How can I archive, to store data that comes from cart to cart object / model, and data that comes from products to product object/model, without loosing their join relationship? 

Please provide examples and explanation, or any other resource that can solve my problem. Thanks for help! 

Like I said, right now I store everything inside ProductModel (PDO::Fetch Class) 

class ProductModel
{
    private int    $productid;
    private string $name;
    private string $description;
    private float  $price;
    private string $imagepath;
    private int $quantity;
    private int $totalprice;
    private int $ordered;
}

My repository goes like this ( $tableName is cart, and $classname is ProductModel -> Above this code) 

    /**
     * Get Cart Items from unique (id) customer
     *
     * @param int $customerId
     * @return bool|array
     */
    public function getCartItems(int $customerId): bool|array
    {
        $tableName = $this->setTableName();
        $className = $this->setClassName();
        $statement = $this->connection->prepare("SELECT cart.quantity, product.productid, product.name, product.imagepath, product.price FROM `$tableName` as cart LEFT JOIN product as product ON product.productid = cart.product_id WHERE cart.customer_id=:customerId AND cart.ordered=0;");
        $statement->execute(['customerId' => $customerId]);
        return $statement->fetchAll(PDO::FETCH_CLASS, $className);
    }

What I want to archive now, is to store the data to relevant classes like this

Product Model (Store Data from Product Table)

class ProductModel
{
    private int    $productid;
    private string $name;
    private string $description;
    private float  $price;
    private string $imagepath;

}

Cart Model (Store Data from Cart Table)

class CartModel
{
    private int $quantity;
    private int $totalprice;
    private int $ordered;
}

[requinix]

Soon you're going to discover that making an ORM is not a simple or quick process. People use things that already exist for a reason.
You do know that you don't need an entire "framework" just for database stuff, right? Libraries like Doctrine can be used just for their own features without you having to adopt all of Symfony too.

There's... honestly, there's so much that needs to be said about what little you've posted that I don't even know where to start.

Why can't you use an existing system for this?

[harakiri1234]

Thanks for you answer @requinix!

I am doing an internship at company. And as my first learning backend project I need to make a Webshop / E-Commerce Website with plain PHP using MVC Pattern  

[requinix]

Then don't create an ORM. You don't need that for an ecommerce thing. It's too complex and you're going to get it wrong - which I don't say because "lol you're an intern" or "I don't think you're smart enough" but because ORMs can get very, very complicated.

If they want you to use MVC then that's fine. You should ask if you can use libraries like Doctrine or Symfony or something to speed up the work, and if they say no then that's troubling.

Honestly, I'm rather concerned that they're giving an intern (no offense) the project of "make an ecommerce website". It's a big can of worms they're asking you to open.

[gizmola]

I would step back from what you have done so far, because it is fairly far away from what any ORM tries to do.  What you have right now is a class that stores SQL.  ORM's "build" sql statements.

Since you are creating this from the ground up, one advantage you have is that you can control the database structure and naming of all tables and attributes.  

This allows you to write code that can take advantage of your table and column naming conventions.

I would suggest you look at CakePHP for inspiration and clarity, as it has a simple but effective "ActiveRecord" type ORM, that starts with conventions which can be customized but don't need to be if your tables and columns were named using the CakePHP conventions.

From the CakePHP Manual:

Quote

By default table objects use an entity class based on naming conventions. For example if your table class is called ArticlesTable the entity would be Article. If the table class was PurchaseOrdersTable the entity would be PurchaseOrder. 

One example of a convention is to have every table have its primary key named "id". If Table 'b' contains a foreign key for Table 'a' then that column will be named 'a_id'.  

If you follow these conventions, then you can write generic base class code that can generate the SQL join syntax you will need to do most things including making a join and storing associated entity data, in for example, a standard One-to-Many relationship.   

You will notice that CakePHP is based on 3 different base classes: 

• Database
  • This is where the DB specific configuration and connections are stored
• Table
  • A Table has an associated Entity, which is your model mapping
  • Handles querying (find)
  • Has built in generic querying methods
  • Custom queries can be added for a specific table
  • Stores "joins" (Associations)
• Entity
  • An entity is your table base class
  • What you use to add a new row
  • Allows you to add mutation code where a column needs some specialized conversion (ie. encryption/decryption)
  • Allows you to define "virtual" columns (fullname = firstname + lastname)

 

Some research that might help you:

• Learn what the ActiveRecord Pattern is.
• Go through the CakePHP code for Database and ORM in their Github repo.
  • In particular look at the Database and ORM folders

 

You will probably notice that just about every ORM has a query builder.  These do the heavy lifting of allowing a developer to write code that can generate the underlying sql needed for even the most complicated queries.  As Requinix already explained, this is complicated code.  You need to figure out how to cut corners and simplify as much as possible, the types of queries you need.  You also need to keep in mind that the point of the ORM is to abstract the underlying database into PHP Objects.  ORM query classes allow the user to work directly with the objects in terms of queries.  Doing anything close to that is a lot of work.  So long as you avoid edge cases and complicated database design, you should be able to avoid having to get into that area, and rely instead on generic/base class code to handle the majority of the work you need to be done.