Custom Query in SQL that uses Custom Fields

Administration
1

Hi,

So the “fields” column in GibbonPerson has the custom field values for each person.
Before I get myself in trouble, no harm in asking: Do you have any clever ideas to use custom fields in Custom Query SQL code?

Thanks!

2

Urko,

Good question…and the answer currently is no. At some point I will need to have an answer though, so let me know how you get on.

The content of those fields is a PHP array serialized into a string, so I guess there is no much we can do on the MySQL side. I wonder if the Query Builder module needs to be adapted so that it can detect output that is a serialised array and output it in a meaningful form. What do you think? Sound like a sensible approach?

Ross

3

Ross,
Well, knowing what it is exactly helps. Google is already giving me some nice tips:
http://stackoverflow.com/questions/5335970/unserialize-through-query-at-database-level-itself
Or this one for simply searching:
http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/

Can you have Custom Queries that are more than one statement?

4

And about the output, yeah, I am sure a lot of people would find it useful, but it sounds like you can only output, in a query, either all the custom fields, or none at all.

In a different school management system, they had solved it by having a separate table for custom field data (not just the definition).
It had external key columns for the Person ID and the Field ID.
Then columns for the different types of custom fields: value_string, value_integer, value_boolean, etc…
So if you knew the id and type of the custom field, you could do a JOIN and take only the value_integer column, for example, ignoring the other columns.
You end up with having to JOIN the same table multiple times to the gibbonPerson table if you need several custom fields, but in the end allows you to tackle database manipulation directly in one SELECT or one UPDATE.

Did this make sense? :smiley:

5

For now, I think I will solve my needs by using LIKE. Don’t need more than that.

6

I’ll try and remember this for future action…when I have some time : )

7

Can you post an example here, just in case anyone else is interested?

8

I will work on it this week, but it’s not really high priority. Watch this space :wink:

9

Hi,
So I’ve written a query that will get me all the Students for which our Parents have accepted that we use their photos for our promotional materials. Custom field of ID 1, options being “Yes”, “No”, “NA”.

Here is a simplified version of the query:

`select surname, firstName, dob, fields from gibbonPerson
where fields REGEXP 's:3:"001";s:[0-9]+:"Yes"'`
```


Now, maybe you think that the ID of the Custom Field could change, or you want to make the query more "future proof". You can pull the ID from the gibbonPersonField table, and concatenate that into the REGEXP:

`select surname, firstName, dob, fields from gibbonPerson
where fields REGEXP CONCAT(
	's:3:"',
	LPAD((SELECT gibbonPersonFieldID from gibbonPersonField
		where name='Photo Release Permission'),3,'0'),
	'";s:[0-9]+:"Yes"')`
```


If you have a very large gibbonPerson table, this kind of SELECT will always read ALL the lines in it, so it could become slow. That concern is left as an exercise to the reader :)
10

This is a more realistic query, searching for currently active students that have said “NO” to using their photos in promotional materials:

`SELECT surname, preferredName, username, dob, nameShort AS rollgroup
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON ( gibbonStudentEnrolment.gibbonPersonID = gibbonPerson.gibbonPersonID )
JOIN gibbonRollGroup ON ( gibbonStudentEnrolment.gibbonRollGroupID = gibbonRollGroup.gibbonRollGroupID )
WHERE STATUS = 'Full'
AND fields REGEXP 's:3:"001";s:[0-9]+:"No"'
AND gibbonStudentEnrolment.gibbonSchoolYearID = (
SELECT gibbonSchoolYearID
FROM gibbonSchoolYear
WHERE STATUS = 'Current')
ORDER BY rollgroup, surname, preferredName`
```
11

One more query, in which I am using MySQL substring and substring_index to extract the values.

  • LOCATE finds the location of the field with ID 001.
  • SUBSTRING gets everything from 15 positions further, where the value actually starts.
  • SUBSTRING_INDEX gets the value of the field, only up to the first quotation mark (").
`select surname, firstName, dob, fields,
SUBSTRING_INDEX(SUBSTRING(fields, LOCATE('s:3:"001";', fields) + 15),'"',1) as 'Can use photo'
from gibbonPerson
where fields REGEXP 's:3:"001";s:[0-9]+:"'`
```
12

:slight_smile: these look amazing, thanks for sharing! Really smart use of SQL

The final one looks like it is to do with privacy options. Gibbon actually has fields to deal with this, but they are turned off by default. Check out Admin > User Admin in the main menu and then Manage User Settings in the module menu.

Ross

13

I’ll take a look at those Privacy options.
And yeah, I’ve written a couple of SQL statements in my life, you could say :wink:
I’m going to plug in dBeaver as a great cross-platform database tool.
http://dbeaver.jkiss.org/

14

is it possible to make use of JSON type column (introduced in mysql 5.7) which provides better query capabilities?

https://dev.mysql.com/doc/refman/5.7/en/json.html

15

Hello, this is one possibility, but we would need some update code in v14 order to take the existing columns and update them. What do you feel are the advantages of JSON? I am guessing it is client-side processing…

I like the questions you are asking, as they will help to push us forward. Keep them coming!

Ross

16

by using a JSON field (postgresql already support such fields now), it’s possible to write custom sql query based on structured info in the JSON field. so instead of hacks using regexp, or using LIKE (which is slow), we can do proper query against data in JSON field. In addition, JSON field also would allow (potentially) databases to create indexes (postgresql does support that IIRC), not sure whether mysql already support that or not.

17

Hi,
The JSON idea looks really good. And it seems MariaDB will also pick it up. I would wait until they do, at least, to make sure the way it gets done in Gibbon will work across the different database backends.
Yes, my Gibbon installation is on MariaDB :smile:

http://serge.frezefond.com/2016/01/mariadb-and-native-json-support/

18

liucougar, this is not a feature I was aware of, but sounds like an interesting way to proceed. I don’t want to rush into this, as I imagine many Gibbon servers are not onto MySQL v5.7 yet, and ask Urko mentions, it would be nice to see it in MariaDB as well.

I have added this as a feature request for v15, and you can see it in our planning board here.

19

json field in mysql 5.7 is actually just a serialized json string: when you write a query to save to this field, it’s just a string, so as long as gibbon is serializing to a valid json string before saving to this field, it does not really matter if it’s a string type column or a json type column.

so I think it could be made transparent: if the database is 5.7 (or for mariadb >=10.2), JSON type column could be created in the db, otherwise just keep it as is. the only change (besides creating different types of field) is to use JSON serialization (instead of php serialization)

20

OK, that is good to know. If this is something you wish to code up, we could look at including it in v14, otherwise I will look into it in v15, time permitting. Ross.