Query Builder Update Broke a Query

kclearylcmc · June 13, 2021, 11:12pm

Greetings!

I was able to upgrade my core build and Query Builder to the most recent versions (21.0.01 and 1.13.06 respectively), but one of my queries (that draws on custom student fields) is no longer working. Can someone help me get this fixed? The query runs fine and most information is correct, but the custom fields for ‘Ensemble’ and ‘School2122’ return as empty. These were working fine when I was on core build 20 and Query Builder 1.8, so I assume it’s something do with the updated database.

Thanks!
Kevin

SELECT gibbonPerson.surname, gibbonPerson.preferredName, gibbonRollGroup.name AS rollGroup, gibbonYearGroup.name AS yearGroup, homeAddress, homeAddressDistrict, gibbonPerson.lockerNumber,
parent1.email AS parent1Email,
CONCAT(parent1.phone1Type,’ ‘,parent1.phone1CountryCode,’ ‘, parent1.phone1) AS phone1, CONCAT(parent1.phone2Type,’ ‘,parent1.phone2CountryCode,’ ‘, parent1.phone2) AS phone2, CONCAT(parent1.phone3Type,’ ‘,parent1.phone3CountryCode,’ ‘, parent1.phone3) AS phone3, CONCAT(parent1.phone4Type,’ ‘,parent1.phone4CountryCode,’ ‘, parent1.phone4) AS phone4,
parent2.email AS parent2Email,
CONCAT(parent2.phone1Type,’ ‘,parent2.phone1CountryCode,’ ‘, parent2.phone1) AS phone1, CONCAT(parent2.phone2Type,’ ‘,parent2.phone2CountryCode,’ ‘, parent2.phone2) AS phone2, CONCAT(parent2.phone3Type,’ ‘,parent2.phone3CountryCode,’ ‘, parent2.phone3) AS phone3, CONCAT(parent2.phone4Type,’ ‘,parent2.phone4CountryCode,’ ‘, parent2.phone4) AS phone4,
CASE WHEN (@find := LOCATE(‘s:3:“006”;s’, gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var := SUBSTRING(gibbonPerson.fields, @find + 15), LOCATE(’“;', @var)-1), '”‘, ‘’) ELSE ‘’ END AS Ensemble,
CASE WHEN (@find := LOCATE(‘s:3:“002”;s’, gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var := SUBSTRING(gibbonPerson.fields, @find + 15), LOCATE(’“;', @var)-1), '”', ‘’) ELSE ‘’ END AS School2122
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID)
JOIN gibbonRollGroup ON (gibbonStudentEnrolment.gibbonRollGroupID=gibbonRollGroup.gibbonRollGroupID)
JOIN gibbonYearGroup ON (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID)
LEFT JOIN gibbonFamilyChild ON (gibbonFamilyChild.gibbonPersonID=gibbonPerson.gibbonPersonID)
LEFT JOIN gibbonFamily ON (gibbonFamilyChild.gibbonFamilyID=gibbonFamily.gibbonFamilyID)
LEFT JOIN gibbonFamilyAdult AS parent1Fam ON (parent1Fam.gibbonFamilyID=gibbonFamily.gibbonFamilyID AND parent1Fam.contactPriority=1)
LEFT JOIN gibbonPerson AS parent1 ON (parent1Fam.gibbonPersonID=parent1.gibbonPersonID AND parent1.status=‘Full’)
LEFT JOIN gibbonFamilyAdult AS parent2Fam ON (parent2Fam.gibbonFamilyID=gibbonFamily.gibbonFamilyID AND parent2Fam.contactPriority=2)
LEFT JOIN gibbonPerson AS parent2 ON (parent2Fam.gibbonPersonID=parent2.gibbonPersonID AND parent2.status=‘Full’)
WHERE gibbonPerson.status=‘Full’
AND gibbonStudentEnrolment.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’)
ORDER BY rollGroup, surname, preferredName

sandra · June 17, 2021, 12:47am

Hi Kevin,

Yes, v21.0.00 updated the custom fields to use JSON data types rather than serialized data, there should have been a note in the changelog but I’m just looking and don’t see it, my apologies.

Here’s an example of accessing a custom field with a CASE, it’s very similar to the previous syntax, with a couple tweaks:

`(CASE WHEN (@find1 := LOCATE('"006":', p.fields)) > 0 THEN REPLACE(LEFT( @var1 := SUBSTRING(p.fields, @find1 + 6), LOCATE('",', @var1)-1), '"', '') ELSE '' END) as `Ensemble``
```


Hope this helps!

kclearylcmc · June 20, 2021, 1:44am

Thank you so much for posting this, Sandra! I figured it was just a matter of syntax or adjusted database entries, but I don’t know enough about JSON data types to figure that out alone.

I am still running into trouble running the query, though. I get this error consistently.

Your request failed with the following error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘p.fields’ in ‘field list’

Here is what I entered to update my code. I guessed a little on the second one because I wasn’t certain what needed to remain and what was specific to what I was calling.

(CASE WHEN (@find1 := LOCATE('"006":', p.fields)) > 0 THEN REPLACE(LEFT( @var1 := SUBSTRING(p.fields, @find1 + 6), LOCATE('",', @var1)-1), '"', '') ELSE '' END) as `Ensemble`,
(CASE WHEN (@find2 := LOCATE('"002":', p.fields)) > 0 THEN REPLACE(LEFT( @var2 := SUBSTRING(p.fields, @find2 + 2), LOCATE('";', @var2)-1), '"', '') ELSE '' END) as `School2122`

As always, help and guidance is appreciated!
Kevin

sandra · June 20, 2021, 2:52am

Hi Kevin, looks like I copied my example from a query that had aliased the gibbonPerson table to just p. In this case, your query should use gibbonPerson.fields rather than p.fields, I’ve updated it in the code below, this should work for your case:

`(CASE WHEN (@find1 := LOCATE('"006":', gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var1 := SUBSTRING(gibbonPerson.fields, @find1 + 6), LOCATE('",', @var1)-1), '"', '') ELSE '' END) as `Ensemble`,
(CASE WHEN (@find2 := LOCATE('"002":', gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var2 := SUBSTRING(gibbonPerson.fields, @find2 + 2), LOCATE('";', @var2)-1), '"', '') ELSE '' END) as `School2122``
```

kclearylcmc · June 20, 2021, 4:14am

The query runs again, Sandra, but it’s still not returning the values entered in those custom fields. The boxes are empty, so there must be something that isn’t lining up.

I’ll experiment a little too, and thank you for correcting my codes.

kclearylcmc · July 29, 2021, 5:29pm

I’ve been messing with this more and can query the first field (School2122) by putting it’s number first in the request. When I do this, though, the second field still doesn’t pull. I’ve tried several variations but cannot figure out what is causing the problem.

Any additional thoughts would be greatly appreciated!
Kevin

kclearylcmc · July 29, 2021, 5:44pm

My son helped me determine a possible explanation for the problem pulling from 006. My 003 and 005 entries in gibbonPerson.fields is long text. He thought that might be messing with the Substring @find1 + 6 instructions.

Not knowing much about JSON syntax, though, this is truly a stab in the dark.

Again, thank you for assistance!

sandra · August 30, 2021, 5:18am

Hi Kelvin, the + 6 isn’t related to the length of the data, but rather the number of characters after the id number that the value starts “002”:` being 6 characters long. In this case, the second line should also be a +6 and not +2.


If you’re running v22, then since there are many new types of custom fields, we increased the length of IDs from 3 to 4. Your query may need to now say (note the extra leading 0 on the id number and the change from +6 to +7):
`
(CASE WHEN (@find1 := LOCATE('"0006":', gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var1 := SUBSTRING(gibbonPerson.fields, @find1 + 7), LOCATE('",', @var1)-1), '"', '') ELSE '' END) as `Ensemble``
```

kclearylcmc · March 13, 2022, 10:04pm

Thank you so much for the reply to my challenges, Sandra–I have been away from Query Building during the school year but am gearing up to need it for our summer programs. I anticipate this well help a lot, but I will keep you posted after I upgrade to v23 and query builder 2.0.04.

Thanks again!
Kevin

kclearylcmc · June 11, 2022, 5:15pm

I’m always struggling with the Query Builder it seems–I appreciate any help that can be offered once again!

I am running Gibbon 23.0.01 right now (I didn’t upgrade to .02 with version 24 on the way) and Query Builder 2.0.04. I can finally get the first line of this (the ensemble data) to run, but the second line pulls as empty.

(CASE WHEN (@find1 := LOCATE(‘“0002”:’, gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var1 := SUBSTRING(gibbonPerson.fields, @find1 + 7), LOCATE(‘",’, @var1)-1), ‘"’, ‘’) ELSE ‘’ END) as Ensemble,
(CASE WHEN (@find2 := LOCATE(‘“0006”:’, gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var2 := SUBSTRING(gibbonPerson.fields, @find2 + 7), LOCATE(‘";’, @var2)-1), ‘"’, ‘’) ELSE ‘’ END) as School2122

It doesn’t matter which of the strings I try to take first (0002 or 0006 or any other number); the first one is always the only one I can pull. I assume, therefore, that I have another syntax error of some kind.

Thank you as always!
Kevin

kclearylcmc · June 11, 2022, 6:05pm

I actually spoke out of turn. I can make the first string return a value for 0006 and 0002 if they are first on the list, but not for 0009 no matter the position.

sandra · June 13, 2022, 12:11am

Hi Kevin, we discovered a similar issue with some of our queries, because the LOCATE is looking for a separator character, and there is no separator when an item is at the end of a string.

Luckily, we’ve also discovered that modern SQL has better methods for handling JSON data, and since we’ve migrated all these types of fields from serialized to JSON data in the past couple versions, there is finally a better way.

There is a special syntax you can use to access an item inside a JSON object, which is much simpler. For your case, try the following:


gibbonPerson.fields->>"$.0002" as Ensemble,
gibbonPerson.fields->>"$.0006" as School2122
`
These should allow you to extract and JSON value using it’s key (in this case, the ID of the custom field). Hope this helps!

kclearylcmc · June 13, 2022, 12:48am

Thanks, Sandra. I have tried a few ways of putting the new code into my query and I’m not getting it to work. (I do like how simple that new syntax is, though!)

Here is the original query.

When I replace the Case When lines directly, I have this query that returns the error at the bottom.

I’m sure that I’m missing something very obvious, but I am not sure what it is. I will keep experimenting but appreciate any clarifications you can offer.

Kevin

sandra · June 14, 2022, 12:21am

Ah, looks like the number needs to be wrapped in quotes, perhaps to cast it as a string. Can you try the following, which has worked for me locally:

`gibbonPerson.fields->>'$."0002"' as fieldName
`
```


Note the single quotes on the outside and double quotes on the inside.

kclearylcmc · June 15, 2022, 3:34am

That did the trick, Sandra! Thank you as always–your contributions to our program are beyond measure.
Kevin

kclearylcmc · June 10, 2023, 9:05pm

It’s time for annual trouble with JSON syntax, Sandra! I am running this query and return the error in the picture.

I’ve tried adjusting in a few different ways that seemed logical (I even tried pulling the “gibbonPerson.fields” request and putting in a working script), but I’m not having luck. Any thoughts or guidance is appreciated!

Kevin

sandra · June 16, 2023, 3:23am

Hmm, interesting. It sounds, based on the error, that some of the fields contain invalid json. You could try wrapping it in a case statement with the JSON_VALID function, see this post for an example: mysql - JSON_EXTRACT not working when not all rows contain JSON - Stack Overflow

kclearylcmc · July 5, 2023, 9:15pm

I get what you are saying, Sandra, but I’m not seeing how to rewrite the query with the proper syntax. (I looked over that post but wasn’t sure how to adapt to what we are working with in the script.) If there is any chance you can provide a sample of how that would look, I would appreciate the boost.

Thanks!
Kevin

sandra · July 21, 2023, 5:13pm

Hi Kevin, sure, perhaps try something like this, replacing:

gibbonPerson.fields->>'$."0006"' as Ensemble,

with:

(CASE WHEN JSON_VALID(gibbonPerson.fields) THEN gibbonPerson.fields->>'$."0006"' ELSE null END) as Ensemble

Hope this helps!

kclearylcmc · July 26, 2023, 7:47pm

It solved the error–thank you, Sandra!
Kevin