Barracuda
4 months ago

Eloquent query returns a field as null when it should not be

Posted 4 months ago by Barracuda

I have two eloquent queries that return the field user_id as null when that should not be possible. Here are the requests:

    /**
     * Return a collection of all the ConcernUser linking a user and the Vem they have not answered that is about to end (in 24h).
     * @return Collection
     */
    public static function concernedByEndingVemsNotAnswered(): Collection
    {
        return ConcernUser::where('concern_type', Vem::class)
            ->join('vems', 'concern_user.concern_id', '=', 'vems.id')
            ->leftJoin('answers', function ($join) {
                $join->on('concern_user.user_id', '=', 'answers.user_id');
                $join->on('concern_user.concern_id', '=', 'answers.answerable_id');
                $join->on('concern_user.concern_type', '=', 'answers.answerable_type');
            })
            ->where('answer_deadline', '<', Carbon::now()->addDay()->toDateTimeString())
            ->where('answer_deadline', '>', Carbon::now()->addHours(23)->toDateTimeString())
            ->where('answerable_id', '=', null)
            ->with(['user', 'concern'])->get();
    }

And

/**
     * Return a collection of all the ConcernUser linking a user and the Administrative Point they have not seen and that is about to reach it's reading end (in 24h).
     * @return Collection
     */
    public static function concernedByEndingAdministrativePointsNotSeen(): Collection
    {
        return ConcernUser::where('concern_type', AdministrativePoint::class)
            ->join('administrative_points', 'concern_user.concern_id', '=', 'administrative_points.id')
            ->leftJoin('seen_indicators', function ($join) {
                $join->on('concern_user.user_id', '=', 'seen_indicators.user_id');
                $join->on('concern_user.concern_id', '=', 'seen_indicators.checkable_id');
                $join->on('concern_user.concern_type', '=', 'seen_indicators.checkable_type');
            })
            ->where('reading_deadline', '<', Carbon::now()->addDay()->toDateTimeString())
            ->where('reading_deadline', '>=', Carbon::now()->addHours(23)->toDateTimeString())
            ->where('checkable_id', '=', null)
            ->with(['user', 'concern'])->get();
    }

The structure of the concern_user pivot table is:

concern_user
    - concern_id    INT(10)         NOT NULL
    - concern_type  VARCHAR(255)    NOT NULL
    - user_id       INT(10)         NOT NULL
    - notified      TINYINT(1)      NOT NULL

I have tested the relationship in a test where I used ConcernUser::all() and all of the fields were properly loaded. However when I use the two previous requests, the field user_id is always null.

Any reason for that?

EDIT: Applying the suggested answer, I now have:

    /**
     * Return a collection of all the ConcernUser linking a user and the Vem they have not answered that is about to end (in 24h).
     * @return Collection
     */
    public static function concernedByEndingVemsNotAnswered(): Collection
    {
        return ConcernUser::select('concern_user.*')
        ->where('concern_type', Vem::class)
            ->join('vems', 'concern_user.concern_id', '=', 'vems.id')
            ->leftJoin('answers', function ($join) {
                $join->on('concern_user.user_id', '=', 'answers.user_id');
                $join->on('concern_user.concern_id', '=', 'answers.answerable_id');
                $join->on('concern_user.concern_type', '=', 'answers.answerable_type');
            })
            ->where('answer_deadline', '<', Carbon::now()->addDay()->toDateTimeString())
            ->where('answer_deadline', '>', Carbon::now()->addHours(23)->toDateTimeString())
            ->where('answerable_id', '=', null)
            ->with(['user', 'concern'])->get();
    }

And it works!

Please sign in or create an account to participate in this conversation.