$DB->get_records() returns only 1 row

$DB->get_records() returns only 1 row

by Perry Way -
Number of replies: 4

I am trying to figure out why $DB->get_records() returns only one row (without having to debug the internal workings of Moodle) when there are multiple rows to be returned. Looks like I will have to set up a test bed where I can do this sort of thing which will take me additional hours to set up another website, database, etc.  I was hoping that someone might know what could interfere with Moodle selecting ALL records in a table matching an ID being passed.

The table I'm selecting from is a view. Could that be a problem? I named the view with "mdl_" preface so it should work and all other $DB calls work for that view so I assumed that using a view would be okay for this one module I'm working on.

Additionally I changed my code so that instead of calling $DB->get_records() I call $DB->get_records_sql() instead. Still I only get one row. There should be more. 

Lastly, in order to determine if there was some sort of Mysql error, when I run the same query in SqlYog, I get multiple rows so I know the problem lies in Moodle code.


Average of ratings: -
In reply to Perry Way

Re: $DB->get_records() returns only 1 row

by James McLean -

Moodle expects the first column returned to be a unique ID; if your view is returning the same value for the first column in each row, this is why - as the first column is treated as the key of the array when building the resultset internally.

Average of ratings: Useful (1)
In reply to Perry Way

Re: $DB->get_records() returns only 1 row

by Andrew Lyons -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Perry,

Perhaps you could paste the code that you've tried here so that we can have a look and point you in the right direction?

You say you're trying to match all rows in the table with the ID specified. Typically IDs are unique so I assume that you're not matching the ID field but another field. If you are matching on IDs, and you have multiple rows which have the same value in the 'id' field, then this is your problem.

Because of the way in which recordsets are converted into records by $DB->get_records(), the first field (typically id) is used as the array key in you list of records. If all of the results have the same id, then they will all be placed into the same key and keep getting overwritten.

I would recommend increasing your debugging level as this will tell you about some common problems.

If you really do need to return multiple records with the same first field value, there are a couple of options to you:

1) use recordsets instead (don't forget to close the recordset when you're done with it). These are iterators rather than an array so you can arrange them into your own datastructure but you cannot treat the in quite the same way;

2) use get_records_sql and have the first field return a concatenation of the unique data items. E.g. 'id || "_" || userid'

Hope this helps,

Andrew

Average of ratings: Useful (1)
In reply to Andrew Lyons

Re: $DB->get_records() returns only 1 row

by Perry Way -

Thanks Andrew and James as well for the generous reply! Now that you mention this I vaguely recall this being a design issue I worked out by using mysqli on another module that integrates to another system, just so I could work unhampered in a traditional dataset mentality.

It so happens that my dataset I want returned is a many result in a one to many relationship and the ID is the parent ID and there are no unique child ID's. So I guess here is one of those places you must invent one in order to overcome the design limitations. I keep scratching my head as to why these limitations were made. The limitations have effectively required bulky designs and iterations, I find often while perusing the code base. Well, whatever the reasons are, I now have the information I needed and thanks guys for providing that! You've been a great help!

Average of ratings: -
In reply to Perry Way

Re: $DB->get_records() returns only 1 row

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

If you don't have a natural unique first column, then us get_recordset_sql instead. That is easy to iterate over using a foreach loop.

If you do have a unique first column (which is almost always the case in Moodle, since every table has a primary key that is a single integer) then it is very nice that get_records returns you an array where the array keys are that primary key.

Would you like to give a link to one specific example of what you consider to be "bulky designs and iterations, I find often while perusing the code base".

Average of ratings: Useful (2)