F
Filament•3w ago
o.m

How to handle custom complex queries in Filaments table

I have a sample query and I wanted to integrate this result and displa it to the table, I also want a search capability. 
group()
            ->users()
            ->whereNotIn('group_users.role_id', $hiddenRoles)
            ->selectRaw("
            users.*,
            (SELECT GROUP_CONCAT(DISTINCT `roles`.`name` SEPARATOR '|') FROM `roles` WHERE `roles`.`id` IN (SELECT `role_id` FROM `group_users` WHERE `group_users`.`user_id` = `users`.`id`)) AS `all_roles`
            ")
group()
            ->users()
            ->whereNotIn('group_users.role_id', $hiddenRoles)
            ->selectRaw("
            users.*,
            (SELECT GROUP_CONCAT(DISTINCT `roles`.`name` SEPARATOR '|') FROM `roles` WHERE `roles`.`id` IN (SELECT `role_id` FROM `group_users` WHERE `group_users`.`user_id` = `users`.`id`)) AS `all_roles`
            ")
2 Replies
toeknee
toeknee•3w ago
on the $table use modifyQuery ? 
            ->modifyQueryUsing(fn ($query) => $query->group()
                ->users()
                ->whereNotIn('group_users.role_id', $hiddenRoles)
                ->selectRaw("
            users.*,
            (SELECT GROUP_CONCAT(DISTINCT `roles`.`name` SEPARATOR '|') FROM `roles` WHERE `roles`.`id` IN (SELECT `role_id` FROM `group_users` WHERE `group_users`.`user_id` = `users`.`id`)) AS `all_roles`
            "))
            ->modifyQueryUsing(fn ($query) => $query->group()
                ->users()
                ->whereNotIn('group_users.role_id', $hiddenRoles)
                ->selectRaw("
            users.*,
            (SELECT GROUP_CONCAT(DISTINCT `roles`.`name` SEPARATOR '|') FROM `roles` WHERE `roles`.`id` IN (SELECT `role_id` FROM `group_users` WHERE `group_users`.`user_id` = `users`.`id`)) AS `all_roles`
            "))
o.m
o.mOP•3w ago
I was able to solved it using fn =>

Did you find this page helpful?