allowedIncludes() method returns corrupted data

[yNaxon]

In Spatie\QueryBuilder\QueryBuilder class, the allowedIncludes method returns corrupted data when including a relationship of type BelongsToMany - when pivot table has a column named the same as related table.

Given the following database:

Table: members

id    |    name
--------------------------------
5         John Doe



Table: groups

id    |    title
--------------------------------
3         teachers
4         students



Table: group_member (pivot)

id    |    group_id (foreign -> groups.id)    |    member_id (foreign -> members.id)
--------------------------------------------------------------------------------
1           3                                                  5
2           4                                                  5

(Note that the pivot table has an id column)

And the following relationships:

class Group extends Model
{
    public function members() {
        return $this->belongsToMany('App\Member', 'group_member', 'group_id', 'member_id');
    }
}

class Member extends Model {
    public function groups() {
        return $this->belongsToMany('App\Group', 'group_member', 'member_id', 'group_id');
    }
}

After allowing inclusion of groups:

$query = QueryBuilder::for(Member::class)
         ->where('id', $id) // $id is a uri parameter
         ->allowedIncludes('groups');

Because both group and group_member tables contain an id column,
Laravel will bind group_member's id column to the query, thus resulting in correct records returned, but with wrong ids.
Dumping groups collection returned from query:

$query = QueryBuilder::for(Member::class)
        ->where('id', $id)
        ->allowedIncludes('groups');

dd($query->first()->groups->toArray());

Will output:

array:2 [
  0 => array:7 [
    "id" => 1
    "title" => "teachers"
    "created_at" => "2018-10-09 14:47:57"
    "updated_at" => "2018-10-09 14:47:57"
    "member_id" => 5
    "group_id" => 3
    "pivot" => array:4 []
  ]
  1 => array:7 [
    "id" => 2
    "title" => "students"
    "member_id" => 1
    "group_id" => 5
    "pivot" => array:4 []
  ]
]

id attribute does not match groups' id in database

After digging inside, I found that allowedIncludes calls addIncludesToQuery method in order to manipulate query.

In the addIncludesToQuery method, the following lines add select statement to the query with given fields:

return [$fullRelationName => function ($query) use ($fields) {
    $query->select($fields);
}];

Because the fields are not prefixed with table name, Laravel's query builder is binding group_member's id column instead of groups's id column.

This is solved by using the already defined method prependFieldsWithTableName:

return [$fullRelationName => function ($query) use ($fields) {

    $fields = $this->prependFieldsWithTableName($fields, $query->getModel()->getTable());

    $query->select($fields);
}];

[freekmurze]

Could you PR a fix with tests for this?

On Tue, 9 Oct 2018 at 19:59, Yonatan Naxon ***@***.***> wrote: In Spatie\QueryBuilder\QueryBuilder class, the allowedIncludes method returns corrupted data when including a relationship of type BelongsToMany - when pivot table has a column named the same as related table. Given the following database: Table: members id | name -------------------------------- 5 John Doe Table: groups id | title -------------------------------- 3 teachers 4 students Table: group_member (pivot) id | group_id (foreign -> groups.id) | member_id (foreign -> members.id) -------------------------------------------------------------------------------- 1 3 5 2 4 5 *(Note that the pivot table has an id column)* And the following relationships: class Group extends Model { public function members() { return $this->belongsToMany('App\Member', 'group_member', 'group_id', 'member_id'); } } class Member extends Model { public function groups() { return $this->belongsToMany('App\Group', 'group_member', 'member_id', 'group_id'); } } After allowing inclusion of groups: $query = QueryBuilder::for(Member::class) ->where('id', $id) // $id is a uri parameter ->allowedIncludes('groups'); Because both group and group_member tables contain an id column, Laravel will bind group_member's id column to the query, thus resulting in correct records returned, but with wrong ids. Dumping groups collection returned from query: $query = QueryBuilder::for(Member::class) ->where('id', $id) ->allowedIncludes('groups'); dd($query->first()->groups->toArray()); Will output: array:2 [ 0 => array:7 [ "id" => 1 "title" => "teachers" "created_at" => "2018-10-09 14:47:57" "updated_at" => "2018-10-09 14:47:57" "member_id" => 5 "group_id" => 3 "pivot" => array:4 [] ] 1 => array:7 [ "id" => 2 "title" => "students" "member_id" => 1 "group_id" => 5 "pivot" => array:4 [] ] ] *id attribute does not match groups' id in database* After digging inside, I found that allowedIncludes calls addIncludesToQuery method in order to manipulate query. In the addIncludesToQuery method, the following lines add select statement to the query with given fields: return [$fullRelationName => function ($query) use ($fields) { $query->select($fields); }]; Because the fields are not prefixed with table name, Laravel's query builder is binding group_member's id column instead of groups's id column. This is solved by using the already defined method prependFieldsWithTableName: return [$fullRelationName => function ($query) use ($fields) { $fields = $this->prependFieldsWithTableName($fields, $query->getModel()->getTable()); $query->select($fields); }]; — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#112>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAdiDZ_6TrrO8zY7cHXASGTxjpHCurkiks5ujOQbgaJpZM4XT0e5> .

-- Freek Van der Herten https://spatie.be +32 495 84 27 91

[rainerkent]

I can PR for this, if @yNaxon doesn't mind.

[yNaxon]

I haven't had the time yet - would be great if you'll PR this!

[AlexVanderbist]

Fixed in #118 - will tag this soon