samedi 1 octobre 2016

Laravel: Get users based on Eloquent relation

I have a User model which has an attribute type among other attributes. Type is used to identify parents and children.

Parent and children (students) have many-to-many relationship. Also students belong to one or many groups (model Group).

User model

/**
 * Filter the scope of users to student type.
 *
 * @param $query
 */
public function scopeStudent($query){
    $query->where('type', '=', 'std');
}

/**
 * Filter the scope of users to parent type.
 *
 * @param $query
 */
public function scopeParent($query){
    $query->where('type', '=', 'prt');
}

/**
 * List of groups the user is associated with.
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function groups(){
    return $this->belongsToMany('\App\Group', 'group_user_assoc')
                ->withTimestamps();
}

/**
 * List of students associated with the user(parent).
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function children(){
    return $this->belongsToMany('\App\User', 'student_parent_assoc', 'parent_id', 'student_id')
                ->withPivot('relation');
}

/**
 * List of parents associated with the user(student).
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function parents(){
    return $this->BelongsToMany('\App\User', 'student_parent_assoc', 'student_id', 'parent_id')
                ->withPivot('relation');
}

The aforementioned relations are working correctly.

Below are my association tables.

student_parent_assoc
----------------------
+------------+------------------+------+-----+
| Field      | Type             | Null | Key |
+------------+------------------+------+-----+
| student_id | int(10) unsigned | NO   | PRI |
| parent_id  | int(10) unsigned | NO   | PRI |
| relation   | varchar(25)      | YES  |     |
+------------+------------------+------+-----+

group_user_assoc
----------------------
+------------+------------------+------+-----+
| Field      | Type             | Null | Key |
+------------+------------------+------+-----+
| group_id   | int(10) unsigned | NO   | MUL |
| user_id    | int(10) unsigned | NO   | MUL |
| created_at | timestamp        | NO   |     |
| updated_at | timestamp        | NO   |     |
+------------+------------------+------+-----+

I need to find students who do not belong to any group along with their parents. I have managed to find students like so

$students = User::student()
                ->whereDoesntHave('groups')
                ->get();

Question: Now I want to find parents of these students. But I am not able to build an eloquent query for the same. How do I do it?

Note: I could get collection of students from above query and run a foreach loop to get their parents like so

$parents = new Collection;
foreach($students as $student) {
    foreach($student->parents as $parent) {
        $parents->push($parent);
    }
}
$parents = $parents->unique();

But somehow I don't think it's very performant when I have to loop through hundreds of objects. Handling this at query level would be much faster.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire