Using get_records_sql() - not all records returned

Using get_records_sql() - not all records returned

by Alex Little -
Number of replies: 10
In my block I'm trying to use the get_records_sql() function and running the SQL in db admin tool gives me 4 results, but the get_records_sql() function only returns 3 records. I've figured out why this is, but I can't figure out a way around it.

I think the reason is because of the associatve array being returned, so one of the fields is used as the key for the array(can't quite determine how it works out which one to use - probably the first in the select statement?).
So the solution ought to be to return an id as first field in the select list.

Unfortunately I can't do this because it's a group by query (grouping on an id number wouldn't quite have the result I want!!), and it's being grouped on 2 fields. So my SQL is along the lines of:

SELECT fieldX, fieldY, COUNT(fieldBlah) FROM mdl_table
GROUP BY fieldX, fieldY

So when my results are returned if there are (legitimately) 2 or more rows with the same value in fieldX, only one of these is returned by get_records_sql().

I see there is the get_recordset_sql() function, but in the comments above the function is says it's 'internal to datalib and should NEVER be called directly' - hence why I'm trying to avoid using that.

I pretty sure someone else must have come across this somewhere else in Moodle, so would be grateful if you'd let me know how you got around it!! Or if anyone has any suggestions as to what I should do wink

cheers, TIA,
Alex


Average of ratings: -
In reply to Alex Little

Re: Using get_records_sql() - not all records returned

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
The only thing I can think of is to use get_recordset_sql().
Average of ratings: -
In reply to Alex Little

Re: Using get_records_sql() - not all records returned

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
We use get_recordset_sql all the time. smile It lets you get around the Moodle 'toy database' model in which databases are dumb containers that can only store record information organised by ID and nothing else. Easy to understand, but limited.

If you're doing GROUP BY, you're already past that, so you might as well use get_recordset_sql.

Should you really really want to keep using get_records_sql, you still can. Here's how:

SELECT random(),* FROM... (etc)

...

...

...yeah.

Don't anyone ever use that code. smile (Or the various similar tricks.)

--sam
Average of ratings: -
In reply to sam marshall

Re: Using get_records_sql() - not all records returned

by Martín Langhoff -
SELECT random() will randomly fail too wink

But I agree - if you understand where get_recordset_sql() differs from get_records_sql() then you know enough to use it. Don't let the comment scare you.
Average of ratings: -
In reply to Martín Langhoff

Re: Using get_records_sql() - not all records returned

by Alex Little -
Thanks all. I'll use get_recordset_sql() then smile
Alex
Average of ratings: -
In reply to Alex Little

Re: Using get_records_sql() - not all records returned

by Pierre Pichet -
There is a similar coding in questiontype/datasetdependent/datasetitems.php (line 111)
$datasetdefs[$defid]->items = get_records_sql( // Use number as key!!
" SELECT number, definition, id, value
FROM {$CFG->prefix}question_dataset_items
WHERE definition = $datasetdef->id ");
}
However get_records_sql put results in an array using number as array parameter so that if you have two records with the same number you will access only one of them!
I had problems with this function...
Could it be the same case?
Pierre
P.S. number has been replaced recently by itemnumber to avoid using a reserved word. I have not tested with the new defs but I don't think that this could change the result.

Average of ratings: -
In reply to Pierre Pichet

Re: Using get_records_sql() - not all records returned

by Alex Little -
Yes, I think it'll be exactly the same reason, but in this case it looks like you could just move the 'id' column to be the first field in the select list and then all would be fine (- assuming id is unique!)
Alex
Average of ratings: -
In reply to Alex Little

Re: Using get_records_sql() - not all records returned

by Pierre Pichet -
Thanks
When recoding the dataitems.php, I did understand ( after some tests) that the first element is the one used , however this is not well documented and this is why you post this item on the forumsmile.
This function rely upon the fact that the first element is unique.
I think that either this should be more documented or that the function should be written in another way ( with all the possible side effects...).

Pierre

P.S. In the following code of dataitems.php (which was code years ago...by somebody else) the number as it is said, is used as $key in a following foreach()...
Average of ratings: -
In reply to Pierre Pichet

Re: Using get_records_sql() - not all records returned

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
As far as I can see you are correct, the documentation is unhelpful. I checked in a change to the doc comments at the top of get_records_sql and a few other functions that are (I think) affected by the same issue [the ones that use recordset_to_array], to document that the first field value should be a unique one such as 'id'. (Change tracked as MDL-7969.)

--sam




Average of ratings: -
In reply to Alex Little

Re: Using get_records_sql() - not all records returned

by Greg Lyon -
Not sure how much more help I'll be here but:

The culprit is the recordset_to_array() function called by get_records_sql(). As you speculate Alex, it uses the first field as an index (sort-of), and only returns 1 row for each.

Good news: You can 'trick' the function by always specifying a field from the 'many' side of your table joins as the first field in your select statement. This seems to work even if the value is duplicated, so your multi-field GROUP BY should still work...but do check me to be sure! (You could always make it even uglier by concatenating your two fields as the 1st field in your select statement, which would guarantee that it works...)

I submitted MDL-7840 about this a couple weeks ago, though I don't exactly see what the solution is.
Average of ratings: -
In reply to Greg Lyon

Re: Using get_records_sql() - not all records returned

by Alex Little -
Cheers Greg, but unfortunately the trick won't work for me - as there's no join - it's just a single table. If I try to add another column then it needs to be grouped by this extra column - which then 'wrecks' the data I actually wanted to return sad
Though I did get around it all by using the get_recordset_sql() function smile

A
Average of ratings: -