How to filter by date in Eloquent relationship

user_7549561 0
Viewed 376

I've two tables first_levels and second_levels the first_levels have a relationship with themselves as parent and child and in the second table, I store the transactions.

my Model:


    public function children()
    {
        return $this->hasMany(first_level::class, 'parent_id', 'Nominal')
            ->with(['children', 'transactions'])
            ->withsum('transactions', 'debit')
            ->withsum('transactions', 'credit');
    }

    public function transactions()
    {
        return $this->hasMany(second_level::class, 'first_level_id', 'id');
    }

my controller:

    function getGeneralLedger(Request $request)
    {
        $items = first_level::with('children')
            ->where('parent_id', 0)
            ->where('company_id', $request->user()->company_id)
            ->get();
        return response()->json(['data' => $items]);
    }

using this model and controller I can get all the data from the first and second tables but when I want to filter the second table based on the created_at column I'm unable to do it.

I tried this method but it didn't work.

    function getGeneralLedger(Request $request)
    {
        $items = first_level::with('children')
            ->where('parent_id', 0)
            ->where('company_id', $request->user()->company_id)
            ->wherebetween('children.transactions.created_at', [$request->from, $request->to])
            ->get();
        return response()->json(['data' => $items]);
    }

I get this error

Column not found: 1054 Unknown column 'children.transactions.created_at' in 'where clause' (SQL: select * from `first_levels` where `parent_id` = 0 and `company_id` = 1 and `children`.`transactions`.`created_at` between 2022-01-10 and 2022-02-09)",
eloquent laravel
1 Answers

try

function getGeneralLedger(Request $request)
{
    $items = first_level::with('children')
        ->where('parent_id', 0)
        ->where('company_id', $request->user()->company_id)
        ->whereHas('children.transactions', function ($q) use ($request) {
            $q->wherebetween('created_at', [$request->from, $request->to]);
        }) 
        ->get();
    return response()->json(['data' => $items]);
}

Source: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

user_129584130
Related