Join

`.join()` method¶

model.py

def join(
        self, 
        model: Model, 
        on_clause: str, 
        join_type: str = "", 
        alias: str = ""
    ) -> JoinResource:

def left_join(...) - > JoinResource:

def right_join(...) - > JoinResource:

def full_join(...) - > JoinResource:

.join() is a method of Model, that joins model(s) by specific on_clause and join_type

model parameter takes Model object that you want to join
on_clause specifies by which id (e.g. data_id) tables will be joined
join_type is the type of join, by default it is set to "" which in terms of sql means INNER JOIN
alias is experimental feature that might be added in future

Four types of .join() method

There are four types of .join() method:

.join() which is default join, i.e. INNER JOIN
.left_join()
.right_join()
.full_join()

Advanced example with two joins¶

Let's examine a case when we want to join three models using .join() method .

Shipping company with two joins

So, for example we have a shipping parcels company: we will create classes TestShippmentOffice, TestCustomer and TestOrder.

And we would like to join these three models in one Resource and read from it using smart query

So we will first define TestShippmentOffice:

from epure.generics import NotNull # (1)!
from epure import escript, epure
from uuid import UUID

@epure()
class TestShippmentOffice:
    adress:NotNull[str]

    def __init__(self, adress) -> None:
        self.adress = adress

Check out about Epure Generics more here

Then create and save instances of it:

Creating and saving TestShippmentOffice instances

office1_id = TestShippmentOffice("Washington str.").save()
office2_id = TestShippmentOffice("Elm str.").save()
office3_id = TestShippmentOffice("Kole str.").save()

We will then define our main class TestCustomer ...

@epure()
class TestCustomer:
    name:str
    country:str

    def __init__(self, name:str, country:str) -> None:
        self.name = name
        self.country = country

... with its smart query read_from_join_resource

We will take Model and Domain of TestCustomer, get Models of other two classes

Model can be taken either through .get_model() method of Domain object or by taking attribute of domain object through . (dot) notation and snakecase name of class:

    @classmethod
    @escript
    def read_from_join_resource(cls):
        model = cls.md # (1)!
        domain = cls.dom # (2)!

        test_order_model = domain.test_order # (3)!

        # test_office_model = domain.test_shippment_office
        # or
        test_office_model = domain.get_model(TestShippmentOffice) #(4)!

Here we are accessing the md: Model object of TestCustomer class. Read more about it here
Here we are accessing dom: Domain object of TestCustomer class. Read more about it here
This way we get Model object of class TestOrder by accessing dom object. Read more about it here
This get_model() method of Model might be more convinient for you if you have class instance in reach. Read more about it here

Then we can just join these three models based on data_id attribute:

        first_join = model.join(test_order_model,\
        model.data_id == test_order_model.test_customer_id) 

        join_res = first_join.join(test_office_model,\
        test_order_model.office_id == test_office_model.data_id)

Or using it like a constructor (chain) of joins:

        join_res = model\
                    .join(test_order_model,\
                    model.data_id == test_order_model.test_customer_id)\
                    .join(test_office_model,\
                    test_order_model.office_id == test_office_model.data_id)

After joining these three modelds, we will have a JoinResource as a result, and we can easily read from it:

        res_no_header = join_res.read(model.country == "Japan")

        # or just:

        res_empty = join_res.read() # will return all entries

Or you can specify colums you want to select:

        res_header = join_res.read(
            [test_office_model.adress, model.name,
            model, test_order_model, model.country], 
            test_office_model.adress == "Washington str.")

Creating and saving TestCustomer instances

nico_id = TestCustomer("Nicolas", "Argentina").save()
victor_id = TestCustomer("Victor", "USA").save()
tom_id = TestCustomer("Tom", "Japan").save()
john_id = TestCustomer("John", "Laos").save()
mike_id = TestCustomer("Mike", "Monaco").save()
bob_id = TestCustomer("Bob", "Netherlands").save()

Defining TestOrder class:

@epure()
class TestOrder:
    test_customer_id:UUID
    order_date:str 
    office_id:UUID

    def __init__(self, test_customer_id, order_date, office_id) -> None:
        self.test_customer_id = test_customer_id
        self.order_date = order_date
        self.office_id = office_id

Creating and saving TestOrder instances

TestOrder(nico_id, "2022-03-15", office1_id).save()
TestOrder(victor_id, "2022-03-10", office2_id).save()
TestOrder(nico_id, "2022-03-15", office1_id).save()
TestOrder(tom_id, "2022-03-30", office1_id).save()
TestOrder(john_id, "2022-01-15", office3_id).save()
TestOrder(mike_id, "2022-12-10", office2_id).save()
TestOrder(nico_id, "2022-08-04", office1_id).save()
TestOrder(bob_id, "2022-09-15", office3_id).save()
TestOrder(bob_id, "2022-05-11", office1_id).save()

Calling test_two_joins method will result in list of lists, each sublist will contain three objects of each type:

TestCustomer.test_two_joins() # ->
    # '[[resource.test_join_resource.TestShippmentOffice object, 
    #      pyt1.tests.resource.test_join_resource.TestCustomer object, 
    #      resource.test_join_resource.TestOrder object], ...]'

Join

.join() method¶

Advanced example with two joins¶

`.join()` method¶