Add Pivot Table support (belongs_to_many)

[circulon]

I see that in Orator there is a facility for working with Pivot Tables
https://orator-orm.com/docs/0.9/orm.html#working-with-pivot-table

Is this a feature of Masonite Orm using the current decorators? or maybe @morph_to (which i'm not sure I understand anyway)

My investigations have only pointed out that I will have a fun time making the traversal work.

Any pointers/snippets would be greatly appreciated

[josephmancuso]

There is actually currently no way to work with many to many tables at the moment through a pivot table.

morph_to is related to polymorphic tables

[josephmancuso]

Changing this ticket from a question to a feature request

[josephmancuso]

Feature Specification

Need to add support for belongs_to_many. This relationship name is the same as orator and should work similiar. This relationship will be for pivot table support

We should be able to have a table structure like the following:

# Table 1
stores
id: int
name: varchar

# Pivot Table
product_store
id: int
store_id: int
product_id: int

# Table 2
products
id: int
name: varchar
price: decimal

We should then be able to go from table1->table2 through the pivot table.

In the models it should look like this:

from masoniteorm.relationships import belongs_to_many

class Store(Model):

    @belongs_to_many
    def products(self):
        from app.models import Product
        return Product

It should also be assumed that the pivot table is ModelX_ModelY LOWERCASE. ModelX and ModelY being in alphabetical order and singular

The keys order should be reversed as well:

@belongs_to(current_model_forign_key, other_model_forign_key, current_model_owner_key, other_model_owner_key)

The SQL for generating the result set should be as follows:

SELECT products.* 
FROM stores 
JOIN product_store ON product_store.store_id = stores.id
JOIN products ON product_store.product_id = products.id
WHERE stores.id = 1

So by default this should look something like:

from masoniteorm.relationships import belongs_to_many

class Store(Model):

    @belongs_to('store_id', 'product_id', 'id', 'id')
    def products(self):
        from app.models import Product
        return Product

We must be able to default these keys

Since we are going directly to the related table and skipping the pivot table we have no real way to get to the pivot. Because of this we should add an attribute on the model to get the pivot. By default this will be pivot but we can change it:

class Store(Model):

    @belongs_to('store_id', 'product_id', 'id', 'id').as('product_pivot')
    def products(self):
        from app.models import Product
        return Product

We can then access the pivot table using:

store = Store.find(1)
store.product_pivot

Lastly these results should return a collection of results

The relationship class should be a standalone class done inside the relationships module.

The tables can be setup and tested in the sqlite database

[josephmancuso]

@circulon Took me all weekend to make sure I added all the features of other ORM's but this is now in release 1.0.7

https://orm.masoniteproject.com/models#has-many-many-to-many

[circulon]

Awesome Work!! Thanks for being so responsive!!

[josephmancuso]

@circulon welcome :)