Suggestion: Inform users about MySQL's View (virtual tables)?

[NicksonYap]

Hi @staudenmeir

I really appreciate the repo

Out of desperation, I've moved a lot of advanced relationships to depend on MySQL's View instead

Creating a fake table, to trick laravel, eager loading, etc has been working

Have been depending on subquery joins lately
also, users should be discouraged to use appends() and use leftJoin() instead

I guess Laravel is built without having deep relationships in mind, which leaves out a lot of functions, causing a lot of limitations

Now I can even union relationships, adding where clauses for the relationships etc.

Would be nice if I knew earlier that Laravel's not capable and MySQL's View is there to help

If I'm right, please add a note to point out MySQL's View is the way to go if the relations are to advanced

In fact, I think Laravel should allow Migrations to create virtual tables

Part of my code that creates a fake relationship, based on the distance of an Address's GPS, to a Polygon of the ServiceArea table. The distance must be less than 0.05

      $query = DB::query()->selectRaw("
            #SELECT
                *
            FROM
                (
                SELECT
                    `crm_addresses`.`id` AS `address_id`,
                    `crm_servicearea`.`id` AS `service_area_id`,
                    `crm_addresses`.`updated_at` AS `updated_at`,
                    `crm_addresses`.`created_at` AS `created_at`,
                    FORMAT(ST_DISTANCE(
                        `crm_servicearea`.`polygon`,
                        `crm_addresses`.`gps`
                    ), 6) AS `distance`
                FROM
                    `crm_addresses`
                INNER JOIN `crm_servicearea` WHERE `crm_addresses`.`gps` IS NOT NULL
                ORDER BY
                    `address_id`,
                    `distance`
            ) AS address_service_area
            WHERE
                `distance` <= 0.05
        ");
        }

then:

        $query_string = getSqlString$query);
        $sql = "create view `{$pivot_table_name}` as {$query_string}";
        DB::statement($sql);

[staudenmeir]

Is this a suggestion for this repository or the Laravel documentation?

[NicksonYap]

Suggestion/Note in this repo to point out that MySQL's view is a possible solution /workaround for highly advanced relationships (with conditions, or unions etc)

While being able to work with eager loading

and then of course when Laravel's Migrations officially support creating MySQL Views + documentation then a lot of us would avoid this headache...

Did you get my reasoning to use MySQL's View?
If it doesn't make sense then it's alright just posting to possible save someone else's time

[staudenmeir]

I really like the view idea, but I don't think it belongs in this repository.

What would be an example for a relationship with conditions?

[NicksonYap]

You're right, its not for this repo but it is an alternative solution

Note that the key problem to solve is advanced/conditional deep relationships.

MySQL queries has no issues with this but eloquent wasn't built for it. (which this repo is trying to solve)

Example:

BillingAccount has many Orders
Order has many Coupons
Coupons has one Invoice
Invoice has many Payment

As you can see, a BillingAccount has Ordered some Coupons which has an Invoice that can be pending Payment or already paid in full (multiple Payments to cover single Invoice)

With MySQL view,
You can create 1 fake relationship pivot table for each relation in Laravel such that, you can get:

BillingAccount->paidCoupons()
BillingAccount->unpaidCoupons()

In total, 2 fake pivot tables

[NicksonYap]

Using MySQL view as pivot table will also solve the Union problem mentioned in: #8

[staudenmeir]

I've created a package for merging relationships using views:
https://github.com/staudenmeir/laravel-merged-relations