Issue with get_records_sql

Issue with get_records_sql

by Jack Eapen -
Number of replies: 2

Hi,

I'm trying the below query in my code (selecting a set of users who are proficient in a set of competencies):

$potentialusersquery="SELECT uc.id as id, uc.userid, u.id, u.alternatename as employee, u.email, u.department, count(*) as count  FROM mdl_competency_usercomp uc

            left join mdl_user u on uc.userid=u.id

            and u.suspended=0

            where uc.userid not in (select userid from mdl_competency_plan where templateid =? and status=1)

            and uc.competencyid in (?)

            and proficiency=1

            group by uc.userid";

$potentialusers=$DB->get_records_sql($potentialusersquery,array($data->orgroles,$competencylist));

 Problem is that count is always returned as 0. when I execute the query directly in DB, there are counts like 4,3,2, etc.

I read about the solution of having a unique id column as the first field for get_records_sql. I believe my query is meeting that requirement. still I'm not getting the required result.

Any clue on where am I going wrong?

Jack

Average of ratings: -
In reply to Jack Eapen

Re: Issue with get_records_sql

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
First two issues, which were the first thign I noticed, even though they are not your issue:

Don't hard-code the table prefix (mdl_) here. You should specify the table name like {user}, and Moodle will add the prefix that is right for this Moodle install.

When doing development, set Admin -> Development -> Debugging to 'Developer' level, then it will warn you if you ever fail to make the first column unique. (And developer debug gives lots other hints too.)

If you are grouping by uc.userid then you cannot use the uc.id in the SELECT list. I think you need to go and re-read the docs on how GROUP BY works.

And, finally, placeholders don't work like this: uc.competencyid in (?). If you have a list of ids, then you need ot use $DB->get_in_or_equals. Otherwise use uc.competencyid = ?
Average of ratings: Useful (4)
In reply to Tim Hunt

Re: Issue with get_records_sql

by Jack Eapen -
thanks Tim for the hints.

I've read your previous posts about developer debug, and that's already ON. but I didn't get any suggestions.

I tried with $DB->get_in_or_equal as you suggested, and got it working. Thanks a ton!

Jack
Average of ratings: -