relation not working

[vhs1092]

Hi!, I am running your example with the User model and it is not working, I am running

$exampleArrayQuery = [
'fields' => ['id', 'first_name', 'created_at'],
'order' => 'first_name',
'include' => [
'permissions' => true,
'roles' => [
'where' => [
'name' => 'xxx',
],
'fields' => ['id', 'name'],
'order' => 'name DESC',
],
],
'offset' => 5,
'limit' => 15,
];

and always return the same data, I dont have a role with name "xxx" and also the data returned does not have the "roles" info

[williamoliveira]

Remove the parts of the query you dont need, like permissions and roles

[vhs1092]

I need the roles, but also in this case I have
$exampleArrayQuery = [
'fields' => ['id', 'first_name'],
'include' => [
'state' => [
'where' => [
'name' => '2222',
],
'fields' => ['id', 'name'],
'order' => 'name DESC',
],
]
];

I want the users with their state name, but returns all the data, the where in the "include" is not working, I am using laravel 5.5 can it be the issue?

[vhs1092]

I tried with laravel 5.2 and the same error,

$exampleArrayQuery = [
'fields' => ['id', 'first_name'],
'include' => [ // relations, can have where, order and fields
'state' => [
'where' => [
'name' => 'adasd',
],
'fields' => ['name']
],
]
];

    $query = User::query();
    $query = $arrayBuilder->apply($query, $exampleArrayQuery);
    
    dd($query->toSql());

is returning "select id, first_name from users where users.deleted_at is null" but the relation is not being applied

[williamoliveira]

The include key translates to with() method of the Laravel Query Builder, if you want to filter out users based on its state do it like this:

$exampleArrayQuery = [
   'where' => [
      'state.name' => 'adasd',
   ],
];

which translates to a whereHas()

[vhs1092]

$exampleArrayQuery = [
'fields' => ['id', 'first_name'],
'include' => [ // relations, can have where, order and fields
'state' => [
'where' => [
'state.name' => 'adasd',
],
'fields' => ['name']
],
]
];

Call to undefined method Illuminate\Database\Query\Builder::state()

User model
public function state()
{
return $this->belongsTo(State::class);
}

State Model

public function users(){
return $this->hasMany(User::class);
}

[williamoliveira]

The where goes on the base:

$exampleArrayQuery = [
	'fields' => ['id', 'first_name'],
	'where' => [
		'state.name' => 'adasd',
	],
	'include' => [
		'state' => [
			'fields' => ['name']
		],
	]
];

[vhs1092]

ok thank you, now the filter is working with this

$exampleArrayQuery = [
'where' => [
'states.name' => 'California',
],
'fields' => ['id', 'first_name'],
'include' => [
'states' => [
'fields' => ['id','name']
],
]
];

It display the users only in "California" but does not have the state data, it shows
id=>null,
first_name=>'John'
states=>null

the query that returns is:

select id, first_name from users
where exists (select * from states where users.state_id = states.id and name = 'California')
and users.deleted_at is null

[vhs1092]

Do you have a working project example? I was not able to let it work :(

[williamoliveira]

I use it on many private projects of mine but I dont have any public.

I dont know what your problem may be, could you try writing the same query using Laravel's query builder and see if it works as you want?

[vhs1092]

Ok I did other test, I have users that have many posts

$exampleArrayQuery = [
'where' => [
'name' => 'GreatAdmin'
],
'include' => [
'posts' => [
'fields' => ['id', 'title']
],
]
];

$query = User::query();
$query = $arrayBuilder->apply($query, $exampleArrayQuery);

should be the same as:

User::with('posts')->where('name', '=', 'GreatAdmin')->get();

Right?, but the second one returns the user "GreatAdmin" with his posts, but the first one only the user info and relations empty :/

relations: array:1 [▼
"posts" => Collection {#483 ▼
#items: []
}
]

[vhs1092]

Now I see the error, there is a conflict with ->select and ->with based on some posts like this
https://stackoverflow.com/questions/40272411/eloquent-select-method-not-working-with-using-with-method

I let it work removing the "fields" on the relation, event the where in the relation is working now

$exampleArrayQuery = [
'where' => [
'name' => 'GreatAdmin'
],
'include' => [
'posts' => [
'where' => [
'title' => 'test',
]
],
]
];

It returned the user with all posts data, the problem will be if I only want some fields of that posts

[williamoliveira]

Oh, good to know, I never ran into this problem cause I rarely use fields

As the Stackoverflow answers says, a workaround like this should work (note the state_id)

$exampleArrayQuery = [
	'fields' => ['state_id', 'id', 'first_name'],
	'where' => [
		'state.name' => 'adasd',
	],
	'include' => [
		'state' => [
			'fields' => ['name']
		],
	]
];