CODEWITHSUNDEEP
laraveleloquent
amine
amine

Reputation: 403

Laravel eloquent join with last record

I have 4 tables: sales, sales_details, products & price. For each sale I have details, details tables' contains a relation to a product, & and each product has many prices (regular, sale).

Sale -> (has many) sales details -> (each sale detail has one) Product -> (has many) Price.

I want to show in jquery datatable for a selected sale : product_name, (last) sale_price, (last) regular_price

-------------------------
-          sales        -
-------------------------
- id         | int      -
- name       | varchar  -
- created_at | datetime -
- updated_at | datetime -
-------------------------

-------------------------
-     sales_details     -
-------------------------
- id         | int      -
- sale_id    | int      -
- product_id | int      -
- created_at | datetime -
- updated_at | datetime -
-------------------------

-------------------------
-       products        -
-------------------------
- id         | int      -
- name       | varchar  -
- created_at | datetime -
- updated_at | datetime -
-------------------------

----------------------------
-          prices          -
----------------------------
- id            | int      -
- product_id    | int      -
- sale_price    | int      -
- regular_price | int      -
- created_at    | datetime -
- updated_at    | datetime -
-----------------------------

Upvotes: 0

Views: 597

Answers (1)

amine
amine

Reputation: 403

I found this solution, it works for me :

$lists = Product::addSelect([
    'sale_price' => Price::select('sale_price')->whereColumn('product_id', 'products.id')->orderBy('id', 'desc')->limit(1),
    'regular_price' => Price::select('regular_price')->whereColumn('product_id', 'products.id')->orderBy('id', 'desc')->limit(1),

    'sales.id as sale_id',
    'sales.name as sale_name',
    'sales.active as sale_active',
    'sales.start as sale_start',
    'sales.end as sale_end',

    'sale_details.id as sale_details_id',
    'sale_details.active as sale_details_active',

    'products.id as product_id',
    'products.name as product_name',
    'products.slug as product_slug',
    \DB::raw("CONCAT(products.name, ' - ', products.slug) as product"),
])
->leftjoin('sale_details', 'sale_details.product_id', '=', 'products.id')
->leftjoin('sales', 'sales.id', '=', 'sale_details.sale_id')
->where('sales.id', '=', $id);

$datatables = DataTables::of($lists);

return $datatables->make(true);

Upvotes: 1

Related Questions