Query Builder Update Broke a Query

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

Comments

  • edited June 17
    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!
  • 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
  • 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`
  • 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.
  • 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
  • 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!
  • 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`
Sign In or Register to comment.