Laravel - Get records between two dates from second table

Question

I have something like this:

Table 1: Training Name, created_at, user_id (Plan_Treninga)

Table 2: user_id, created_at, expire_at (InvoiceUser)

I want to pull all from Table 1 where created_at is between Table 2 created_at and expire_at.

This is something what i am trying to..

$plan = Plan_Treninga::whereBetween(function($q) use ($id){
          $inv = InvoiceUser::where([
            ["user_id",$id],
            ["status","paid"],
          ])->latest("id")->first();
        })

I haven't finished it yet, but my brain stopped working so I have to ask here.

No.. The main idea is to get all "Training Plans" created in period of his package duration. Also, invoiceuser will have more records and i want to have something like this: Invoice 1# ( ex. 24.05.2020 - 25.05 2020 - this date is from Invoice) 26 training plans - this is from Plan_Treninga Invocie 2# ( ex. 25.05.2020 - 25.06-2020 ) 20 training plans. Also i want to group it. — Nikola Ranisavljev
– Nikola Ranisavljev, Commented May 29, 2020 at 11:24
Where exactly are you using created_at or expire_at in that query? — user8034901
– user8034901, Commented May 29, 2020 at 11:33
Nowhere, i started and didn't finish it. Its fixed, thanks for reply. — Nikola Ranisavljev
– Nikola Ranisavljev, Commented May 29, 2020 at 12:34

Sok Hai · Accepted Answer · 2020-05-29 11:31:55Z

2

If I understand what you want clearly is. you want to query all from table 1 which created exist between table 2 created and expire_at right? if so you can use where exist query to achieve this.

// assume your table name is plan_treningas & invoice_users
Plan_Treninga::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('invoice_users')
          ->whereRaw('plan_treningas.created_at BETWEEN invoice_users.created_at AND invoice_users.expire_at'); // add more query depend your logic
})->get();

for more you can take a look at docs

or if you want to use raw query

SELECT
*
FROM plan_treningas
WHERE EXISTS (
    SELECT 1 FROM invoice_users WHERE plan_treningas.created_at BETWEEN invoice_users.created_at AND invoice_users.expire_at
)

answered May 29, 2020 at 11:31

Sok Hai

5464 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Nikola Ranisavljev Over a year ago

$plan = Plan_Treninga::select("name","id","user_id")->where([["user_id",$id],["trainer_id",$user->id]])->whereExists(function ($query) {                $query->select("name")                      ->from('invoice_user')                      ->whereRaw('plan_treninga.created_at BETWEEN invoice_user.created_at AND invoice_user.expire_at'); // add more query depend your logic             })->get();

Little modification and its working. God bless you!

Sok Hai Over a year ago

@NikolaRanisavljev great it can help you

Nikola Ranisavljev Over a year ago

how can I group them as invoice_user.id?

Sok Hai Over a year ago

@NikolaRanisavljev did this 2 table relate to each other?

Nikola Ranisavljev Over a year ago

Nope, they aren't.

|

Sprep · Accepted Answer · 2020-05-29 11:32:46Z

1

Take a look at joins https://laravel.com/docs/7.x/queries#joins

I am not saying this is the exact solution but I have something similar that I have changed to point you in the right direction.

With joins you can do lots of things.

$results = DB::table('table1')
            ->join('table2', function ($join) {
                $join->on('table1.user_id', '=', 'table2.user_id')
                    ->where('table2.status', '=', 'paid')
                    ->where('table2.created_at', '>', 'table1.created_at');
            })
            ->get();

Also look at relationships. There is some good answers for setting up many to many relationships.

https://laravel.com/docs/7.x/eloquent-relationships#many-to-many

answered May 29, 2020 at 11:32

Sprep

56210 silver badges21 bronze badges

2 Comments

Nikola Ranisavljev Over a year ago

Answer up fixed my problem. Thanks for solution anyways! I am using Laravel 5.8, is it complicated and painful if i upgrade it to laravel 7?

Sprep Over a year ago

Hi glad I could help. I am not sure about upgrading as I started on this version. Upgrading sounds painful, perhaps set up a new setup as a micro service?

Collectives™ on Stack Overflow

Laravel - Get records between two dates from second table

2 Answers 2

13 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related