{"action":"created","issue":{"url":"https://api.github.com/repos/yajra/laravel-datatables/issues/762","repository_url":"https://api.github.com/repos/yajra/laravel-datatables","labels_url":"https://api.github.com/repos/yajra/laravel-datatables/issues/762/labels{/name}","comments_url":"https://api.github.com/repos/yajra/laravel-datatables/issues/762/comments","events_url":"https://api.github.com/repos/yajra/laravel-datatables/issues/762/events","html_url":"https://github.com/yajra/laravel-datatables/issues/762","id":178574708,"number":762,"title":"Having clause in the filterColumn callback not working","user":{"login":"JanKyselica","id":6184477,"avatar_url":"https://avatars.githubusercontent.com/u/6184477?v=3","gravatar_id":"","url":"https://api.github.com/users/JanKyselica","html_url":"https://github.com/JanKyselica","followers_url":"https://api.github.com/users/JanKyselica/followers","following_url":"https://api.github.com/users/JanKyselica/following{/other_user}","gists_url":"https://api.github.com/users/JanKyselica/gists{/gist_id}","starred_url":"https://api.github.com/users/JanKyselica/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JanKyselica/subscriptions","organizations_url":"https://api.github.com/users/JanKyselica/orgs","repos_url":"https://api.github.com/users/JanKyselica/repos","events_url":"https://api.github.com/users/JanKyselica/events{/privacy}","received_events_url":"https://api.github.com/users/JanKyselica/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yajra/laravel-datatables/labels/need%20feedback","name":"need feedback","color":"f9d0c4"},{"url":"https://api.github.com/repos/yajra/laravel-datatables/labels/question","name":"question","color":"cc317c"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-09-22T10:36:25Z","updated_at":"2016-09-29T09:04:39Z","closed_at":null,"body":"I have havingRaw clause in the filterColumn callback but resulted query doesn't contain this clause.\r\n\r\n### Code snippet of problem\r\n\r\n```php\r\n$query = Download::query()->select(\\DB::raw('chk_downloads.*, count(chk_results.team_id) as results'))\r\n            ->leftJoin('results',function($join) {\r\n                $join->on('downloads.quizz_id', '=', 'results.quizz_id')\r\n                    ->on('downloads.week', '=', 'results.week');\r\n            })\r\n           ->groupBy('downloads.id');\r\n\r\nreturn $this->getDatatable($query)            \r\n            ->filterColumn('result', function($query, $keyword) {     \r\n            // result column is searchable and its value is in the request\r\n            // dd($keyword) -> string ('yes')\r\n                if($keyword == 'yes') {\r\n                    $query->havingRaw('COUNT(chk_result.team_id) > 0');\r\n                } else {\r\n                    $query->havingRaw('COUNT(chk_result.team_id) = 0');\r\n                }\r\n            })\r\n            ->make(true);\r\n    }\r\n```\r\n\r\nQuery from debugbar after selecting 'yes' option:\r\n```sql\r\nselect chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` and `chk_downloads`.`week` = `chk_results`.`week` where `chk_downloads`.`deleted_at` is null group by `chk_downloads`.`id`\r\n```\r\n### System details\r\n\r\n- Operating System -> Ubuntu 16.04\r\n- PHP Version -> PHP 7.0.10\r\n- Laravel Version -> Laravel Framework version 5.2.45\r\n- Laravel-Datatables Version -> 6.11\r\n"},"comment":{"url":"https://api.github.com/repos/yajra/laravel-datatables/issues/comments/250411166","html_url":"https://github.com/yajra/laravel-datatables/issues/762#issuecomment-250411166","issue_url":"https://api.github.com/repos/yajra/laravel-datatables/issues/762","id":250411166,"user":{"login":"JanKyselica","id":6184477,"avatar_url":"https://avatars.githubusercontent.com/u/6184477?v=3","gravatar_id":"","url":"https://api.github.com/users/JanKyselica","html_url":"https://github.com/JanKyselica","followers_url":"https://api.github.com/users/JanKyselica/followers","following_url":"https://api.github.com/users/JanKyselica/following{/other_user}","gists_url":"https://api.github.com/users/JanKyselica/gists{/gist_id}","starred_url":"https://api.github.com/users/JanKyselica/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JanKyselica/subscriptions","organizations_url":"https://api.github.com/users/JanKyselica/orgs","repos_url":"https://api.github.com/users/JanKyselica/repos","events_url":"https://api.github.com/users/JanKyselica/events{/privacy}","received_events_url":"https://api.github.com/users/JanKyselica/received_events","type":"User","site_admin":false},"created_at":"2016-09-29T09:04:39Z","updated_at":"2016-09-29T09:04:39Z","body":"Sorry for late response, I have been on holiday.\r\n\r\nThere are all three queries after selecting 'yes' option:\r\n```sql\r\nselect count(*) as aggregate from \r\n    (select chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` \r\n         left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` \r\n               and `chk_downloads`.`week` = `chk_results`.`week` \r\n         where `chk_downloads`.`deleted_at` is null group by `chk_downloads`.`id` \r\n         order by `downloaded_at` desc) count_row_table\r\n\r\nselect count(*) as aggregate from \r\n     (select chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` \r\n          left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` \r\n                and `chk_downloads`.`week` = `chk_results`.`week` \r\n          where `chk_downloads`.`deleted_at` is null group by `chk_downloads`.`id` \r\n          order by `downloaded_at` desc) count_row_table\r\n\r\nselect chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` \r\n     left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` \r\n          and `chk_downloads`.`week` = `chk_results`.`week` \r\n     where `chk_downloads`.`deleted_at` is null group by `chk_downloads`.`id` \r\n     order by `downloaded_at` desc limit 75 offset 0\r\n```\r\n\r\nStill missing havingRaw clause from 'result' column callback.\r\n\r\n## Working workaround ##\r\n \r\n```php\r\n$query = Download::query()->select(\\DB::raw('chk_downloads.*, count(chk_results.team_id) as results'))\r\n            ->leftJoin('results',function($join) {\r\n                $join->on('downloads.quizz_id', '=', 'results.quizz_id')\r\n                    ->on('downloads.week', '=', 'results.week');\r\n            });        \r\n        $columns = $request->get('columns');\r\n        // result column has index 5\r\n        if(isset($columns[5]['search']['value']) && !empty($columns[5]['search']['value'])) {\r\n            $keyword = $columns[5]['search']['value'];\r\n            if($keyword == 'yes') {\r\n                $query->havingRaw('COUNT(chk_results.team_id) > 0');\r\n            } else {\r\n                $query->havingRaw('COUNT(chk_results.team_id) = 0');\r\n            }\r\n        }\r\n        $query->groupBy('downloads.id');\r\n\r\nreturn $this->getDatatable($query)            \r\n            ->filterColumn('result', function($query, $keyword) {\r\n                // must be empty                \r\n            })\r\n            ->make(true);\r\n```\r\n\r\nGenerated queries:\r\n\r\n```sql\r\nselect count(*) as aggregate from \r\n    (select chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` \r\n          left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` \r\n                and `chk_downloads`.`week` = `chk_results`.`week` \r\n          where `chk_downloads`.`deleted_at` is null \r\n          group by `chk_downloads`.`id` \r\n          having COUNT(chk_results.team_id) > 0 order by `downloaded_at` desc) count_row_table\r\n\r\nselect count(*) as aggregate from \r\n     (select chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` \r\n          left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` \r\n                and `chk_downloads`.`week` = `chk_results`.`week` \r\n          where `chk_downloads`.`deleted_at` is null \r\n          group by `chk_downloads`.`id` \r\n          having COUNT(chk_results.team_id) > 0 order by `downloaded_at` desc) count_row_table\r\n\r\nselect chk_downloads.*, count(chk_results.team_id) as results from `chk_downloads` \r\n    left join `chk_results` on `chk_downloads`.`quizz_id` = `chk_results`.`quizz_id` \r\n          and `chk_downloads`.`week` = `chk_results`.`week` \r\n    where `chk_downloads`.`deleted_at` is null \r\n    group by `chk_downloads`.`id` \r\n    having COUNT(chk_results.team_id) > 0 \r\n    order by `downloaded_at` desc limit 75 offset 0\r\n```\r\n"}}
{"actor":{"display_login":"JanKyselica"}}