CODEWITHSUNDEEP
laraveldateeloquent
Nikola Ranisavljev
Nikola Ranisavljev

Reputation: 65

Laravel - Get records between two dates from second table

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.

Upvotes: 0

Views: 321

Answers (2)

Sprep
Sprep

Reputation: 560

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

Upvotes: 1

Sok Hai
Sok Hai

Reputation: 546

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
)

Upvotes: 2

Related Questions