So in my laravel application i made a filter for the 'file' field.
i made this filter for different roles (admin, partner) First i made it working on the partner side (this shows all the file's accesable for that role (not all the files) and this works 100%. but now i want to make it working for the admin, (acces to all the file's) but this just does not work because it always returns this error:
SQLSTATE[HY093]: Invalid parameter number
SQL:
select * from `file` where `id` in (9, 11, 9, 11, 9, 11, 9, 11, 9, 11)
As you can see there are duplicate entry's . is there a way to ignore this error and just load in the file's. if i copy paste the query in SQL itself, it just works.
code for the admin filters:
if ($tag_id != 0 && $langs != 0) {
for ($i = 0; $i < $count; $i ) {
$fileCount = File_Tag::where('tag_id', $tag_id)->pluck('file_id')->count();
$file[] = $fileCount > 0 ? File_Tag::where('tag_id', $tag_id)->pluck('file_id') : null;
}
$this->AdminFiles = File::whereIn('id', $file)->where('language_id', $langs)->get();
} else if ($tag_id != 0) {
for ($i = 0; $i < $count; $i ) {
$fileCount = File_Tag::where('tag_id', $tag_id)->pluck('file_id')->count();
$file[] = $fileCount > 0 ? File_Tag::where('tag_id', $tag_id)->pluck('file_id') : null;
}
$this->AdminFiles = File::whereIn('id', $file)->get();
} else if ($langs != 0) {
$this->files = File::where('language_id', $langs)->get();
} else {
$this->AdminFiles = File::all();
}
code for the partner filter (working)
if($tag_id != 0 && $langs != 0)
{
for ($i = 0; $i < count($file_role); $i ) {
$fileCount = File_Tag::where('tag_id', $tag_id)->where('file_id', '=', $file_role[$i])->pluck('file_id')->count();
$file[] = $fileCount > 0 ? File_Tag::where('tag_id', $tag_id)->where('file_id', '=', $file_role[$i])->pluck('file_id') : null;
}
$this->files = File::whereIn('id', $file)->where('language_id', $langs)->get();
}
else if ($tag_id != 0) {
for ($i = 0; $i < count($file_role); $i ) {
$fileCount = File_Tag::where('tag_id', $tag_id)->where('file_id', '=', $file_role[$i])->pluck('file_id')->count();
$file[] = $fileCount > 0 ? File_Tag::where('tag_id', $tag_id)->where('file_id', '=', $file_role[$i])->pluck('file_id') : null;
}
$this->files = File::whereIn('id', $file)->get();
}
else if($langs != 0) {
$this->files = File::whereIn('id', $file_role)->where('language_id', $langs)->get();
}
else {
$this->files = File::whereIn('id',$file_role)->get();
}
CodePudding user response:
As far as I can see, you want to filter files based on (1) a given file tag (if present) and also filter them based on (2) a given set of languages (if present). The (3) role part is not that clear for me but I think you can adjust the following code to include that constraint too.
Whenever you want to apply constraints in your query base on conditions, you can make use of Conditional Clauses.
This just apply the constraint to the query when a condition is met.
(1) Filter based on the role tag. You can create the relationship between File and FileTag to then use the relationship to filter values between the tables:
$files = File::query()
// This tells Laravel than whenever $tag_id != 0, will apply the filter
->when($tag_id != 0, function ($query) use ($tag_id) {
$query->whereHas('tags', function ($query) use ($tag_id) {
$query->where('id', $tag_id);
}
})
->get();
(2) Then, you want to also apply a filter if a language is provided:
$files = File::query()
->when($langs, function ($query) use ($langs) {
$query->where('language_id', $langs);
})
->get();
The cool part is that you can chain them. So at the end you can do:
$files = File::query()
->when($tag_id != 0, function ($query) use ($tag_id) {
$query->whereHas('tags', function ($query) use ($tag_id) {
$query->where('id', $tag_id);
}
})
->when($langs, function ($query) use ($langs) {
$query->where('language_id', $langs);
})
->get();