Converting SQL Queries Strategy

This looks exactly like something I would have written in my pre-laracasts days.

Speaking generally, I'd first work on finding your models, then defining your relationships, and then building your constraints/queries. It seems at minimum, you have the following models/tables:

Product

Review

• product_review pivot table

Discount

• discount_product pivot table

Customer

Group

• customer_group pivot table

Category

• category_product pivot table
• category_subcategory pivot table

SubCategory

---

Then you can start to define your Eloquent relationships. A Product has many Reviews. A Discount can belong to many Products. A Customer belongs to one (or many) Groups. A SubCategory belongs to one Category. etc.

Once you have working model structure and relationship definitions, you can build out your queries. Your queries may not be the most 'eloquent' given their complexity, but you can use the query builder to mix fluent 'where' or 'whereHas' with 'DB::raw' queries to get what you need.

Heh - that looks like code I'm dealing with just now - only it's mixed with html and doesn't have any indentation (or where it does, it's pretty much random - woo!).

My process was pretty much to figure out what the query was doing and writing it out in as plain php - not really relationships or the like. Just building arrays/collections, foreach'ing over them etc - just to get it clear that I had the query right and it returned the same data. Then it was a lot clearer to me how to set up & refactor the relations with eloquent. If the queries you're dealing with a readable though you can probably skip some of that - though for me it was invaluable to build up a picture of how different bits of the system related.

Below is the first bit of the code I'm dealing with - this is actually one of the clearer bits too! No comments naturally and the method names very rarely give any clues (this one is in 'function check()' ) :-)

        $query = 'select system_id,jobname,holder,created,thickness, (select round(0.5*sum(runtime)) from ';
        $query .= 'bookedjobs  where userid=vj.userid and created > ';
        $query .= 'unix_timestamp(now())-24*7*3600)+ (select count(system_id) from bookedjobs ';
        $query .= 'where userid=vj.userid and created > unix_timestamp(now())-24*7*3600 and ';
        $query .= '(system_id<vj.system_id or status="ok"))+ (select count(registration=1) ';
        $query .= 'from bookedjobs  where userid=vj.userid and created > ';
        $query .= 'unix_timestamp(now())-24*7*3600 and (system_id<vj.system_id or ';
        $query .= 'status="ok")) + round((created-unix_timestamp(now()))/24/3600/2) ';
        $query .= 'as Priority,status,jobtime,registration,subs from bookedjobs vj ';
        $query .= 'where created > unix_timestamp(now())-24*7*3600 and (status="pending" ';
        $query .= 'or status="loaded") ';

@ohffs

Wow I thought I had it bad ... at least my rewrites are all factored into their own layer. Best of luck with yours :)

@jasrys

Unfortunately I don't have the ability to change any of the existing db structure either, this API needs to work with a native freshly installed OpenCart installation with a couple additional new files being added to direct the built in calls to models to the API vs a local db.

So for my eloquent models I'm already having to add quite a few properties to fit the existing tables.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model {

    /**
     * Opencart table.
     *
     * @var string
     */
    protected $table = 'product';

    /**
     * Table primary key.
     *
     * @var string
     */
    protected $primaryKey = 'product_id';

    /**
     * Opencart doesn't have timestamps by default.
     *
     * @var bool
     */
    public $timestamps = false;

    /**
     * Create carbon instances for date fields.
     *
     * @var array \Carbon\Carbon
     */
    protected $dates = ['date_available', 'date_added', 'date_modified'];

    /**
     * Table columns that are fillable.
     *
     * @var array
     */
    protected $fillable = [
        'model', 'sku', 'upc', 'ean', 'jan', 'isbn', 'mpn', 'location', 'quantity',
        'stock_status_id', 'image', 'manufacturer_id', 'shipping', 'price', 'points',
        'tax_class_id', 'date_available', 'weight', 'weight_class_id', 'length',
        'width', 'height', 'length_class_id', 'subtract', 'minimum', 'sort_order',
        'status', 'viewed', 'date_added', 'date_modified',
    ];

    /*
    |--------------------------------------------------------------------------
    |  Place all your relationships below this comment.
    |--------------------------------------------------------------------------
    */

}

And of course the existing pivot tables aren't named correctly so there's that to deal with. Is there something in a model that I need to do specifically to set a given model as a pivot table? Or do I not make models for those tables?

I appreciate everyone's help a lot.

@jasrys

Disregard that last question, I didn't see the docs on defining a pivot table in the relationships, duh.

I do have another question though. In looking through the schema for the OpenCart DB I find product_id in many of the tables.

How do I decide whether a given table is meant to be a pivot table, or if I should create a model for a given table. Take this example for instance.

The below schema has a column for product_id:

DROP TABLE IF EXISTS `oc_coupon_product`;
CREATE TABLE `oc_coupon_product` (
  `coupon_product_id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`coupon_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

But I wouldn't think this would be a pivot table since it has it's own primary key, but it's primary responsibility in the system is to hold data for a coupon specified for a specific product, so perhaps I'm wrong.

I guess what I meant by strategy in my topic title is how do I scan through these classes and determine what relationships to build? Which tables should be used as pivot and which need their own models?

I think once I have all these relationships correct, building the queries should be fairly straight forward using all the tools in Eloquent.

Thanks again for the help.

I'm so glad I don't have to do that ... Anyways when I have to do that kind of stuff, I try to "draw" what needs to happen during a request: I want to get some kind of Products, so I need a Product model. I want to get the name of the current user so I need a User model. I want this User to purchase thoses product, so I need an Order model. I need to apply a coupon to a specific product, so I need a Coupon model. However, I don't need a model to apply a coupon to a product, so a model is not necessary and a pivot table useful. (Ideally if I have to create a new coupon/product association, I'd like to do : Product::where('name', 'laravel')->applyCoupon($coupon) or smt)

If the name of the table is stuff_thing, id's say it's intended to be a pivot table.

Anyways good luck !

How do I decide whether a given table is meant to be a pivot table, or if I should create a model for a given table.

But I wouldn't think this would be a pivot table since it has it's own primary key, but it's primary responsibility in the system is to hold data for a coupon specified for a specific product, so perhaps I'm wrong.

A pivot table is needed when you have a one-to-many or many-to-many relationship. In Laravel, your pivot tables are still "models" in the sense you'll use php artisan make:model to create them and they'll have their own extends Model class and file, but they really often serve merely as a way to define relationships between other models. If, for example, a User can have many Orders (a one-to-many relationship), you'll have a User model, an Order model and an OrderUser model (which is your pivot table). The schema might look something like this:

User

• id (primary key)
• first_name
• last_name
• email

Order

• id (primary key)
• description
• amount

OrderUser

• id (primary key)
• order_id (foreign key)
• user_id (foreign key)

Note that while the OrderUser table/model has a primary key of its own, it's serving a bridge (or "pivot") between the User and Order models.

In your example, the oc_coupon_product table is akin to the OrderUser table/model in the Users/Orders example. It exists to serve as a bridge between (guessing names here) an oc_coupon table/model and an oc_product table/model. Whenever you see a table with just two columns referencing ids on other tables/models, it's a giveaway that you're looking a pivot table.