how to use exact pivot table relationship

Question

i have four table in database(sql):

customers Table:

id
name

credits Table:

id
name
count

customer_credit Table (pivot table):

customer_id
credit_id
expiry_date

consumers Table:

id
customer_id

this is the SPA application(Web base) with laravel. how to received (only login customers) records from consumers table who conditions in customer_credit table available records without expire date for we can computing remaining credits. more explain is we are trying to gathering entrance and exit logs from guests in our pool and gym and we want to gets count in credits table who exact customer_credit table id .

Answer 1

First option, to use Laravel Query Builder:

DB::table('consumers')
     ->select('consumers.*')
     ->join('customers', 'consumers.customer_id', '=', 'customers.id')
     ->join('customer_credit', 'customers.id', '=', 'customer_credit.customer_id')
     ->join('credits', 'customer_credit.credit_id', '=', 'credits.id')
     ->where('credits.count', '>', 0)
     ->where('customer_credit.expiry_date', '>=', date('Y-m-d'))
     ->get();

Second option, to use Laravel Eloquent. If you choose the second one and need help, you should share your model structures here.

Also read this topic to see the differences: Laravel Eloquent vs query builder - Why use eloquent to decrease performance