Laravel filter relationship data

Question

Code

public function getCauserDetails(){
   return $this->belongsTo(Users::class, 'causer_id', 'id');
}

$name = testtt; //data to find

ActivityLog::where('causer_id', $userid)
    ->orWhere('subject_id', $userid)
    ->with('getCauserDetails')
    ->whereHas('getCauserDetails', function ($q) use ($name) {
        $q->where('name', "=", 'testtt');
    })
    ->get()
    ->toArray();

Data return

Array
(
    [0] => Array
        (
            [id] => 22
            [log_name] => login
            [subject_type] => App\Models\Users
            [subject_id] => 0
            [causer_id] => 2
            [created_at] => 2021-05-18 21:27:07
            [updated_at] => 2021-05-18 21:27:07
            [get_causer_details] => Array
                (
                    [id] => 2
                    [name] => lim
                )
        )

    [1] => Array
        (
            [id] => 21
            [log_name] => logout
            [subject_type] => App\Models\Users
            [subject_id] => 0
            [causer_id] => 2
            [created_at] => 2021-05-18 21:26:57
            [updated_at] => 2021-05-18 21:26:57
            [get_causer_details] => Array
                (
                    [id] => 2
                    [name] => Senior
                )

        )

    [2] => Array
        (
            [id] => 22
            [log_name] => logout
            [subject_type] => App\Models\Users
            [subject_id] => 0
            [causer_id] => 2
            [created_at] => 2021-05-18 21:26:57
            [updated_at] => 2021-05-18 21:26:57
            [get_causer_details] => Array
                (
                    [id] => 2
                    [name] => testtt
                )

        )
)

Question: I tried to filtering data that if a relationship "getCauserDetails", How can I not show the data if the relationship name is only found "test" or should I say null? So on the example output, it displays all the data, but I want to only the data that I filter just retrieve.

the question is, why are you keeping test data in your database ? — N69S, May 18 '21 at 14:14

shaedrich · Accepted Answer · 2021-05-18T14:42:06.030

3

Because of your orWhere() you need to nest it:

$name = 'testtt'; //data to find

ActivityLog::with('getCauserDetails')
    ->where(function($q) {
        $q->where('causer_id', $userid)
        ->orWhere('subject_id', $userid)
    })
    ->whereHas('getCauserDetails', function ($q) use ($name) {
        $q->where('name', "=", $name);
    })
    ->get()
    ->toArray();

You can filter the relation, too:

$name = 'testtt'; //data to find
ActivityLog::where('causer_id', $userid)
    ->orWhere('subject_id', $userid)
    ->with(['getCauserDetails' => function ($q) use ($name) {
        $q->where('name', "=", $name);
    }])
    ->whereHas('getCauserDetails', function ($q) use ($name) {
        $q->where('name', "=", $name);
    })
    ->get()
    ->toArray();

edited May 18 '21 at 14:42

answered May 18 '21 at 14:13

shaedrich

5,457
3
26
42

seems like this query is just to filter the column data which is "get_causer_details" become empty if not match, but other data will show. I want the data is only returned the data match, so will only have one array which is [2] get_causer_details.name == testtt – ventures 999 May 18 '21 at 14:20
1

Thanks a lot. Finally solved it. Stuck at the query for so long. – ventures 999 May 18 '21 at 14:44
Yeah, the level of abstraction ORMs like Eloquent come with sometimes make it hard to spot some flaws that might be more obvious in plain SQL ^^ – shaedrich May 18 '21 at 14:49

Laravel filter relationship data

1 Answers1