Created
March 21, 2025 16:42
-
-
Save rcassani/f9da1026053a8dad3c9ce0ad3c4be492 to your computer and use it in GitHub Desktop.
SQLite COALESCE in Brainstorm DB
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| %% SQLite queries to Brainstorm database can be faster by using JOIN on tables | |
| % The existence of Raw and NoRaw Subjects complicates the JOIN as for | |
| % NoRaw subjects it is needed to copy some fields from the DefaultAnatomy | |
| % and this is done with the function COALESCE | |
| % However, using COALESCE, we must write down all the fields, as '*' is not supported | |
| % | |
| % See examples below | |
| %% Non-raw Subject | |
| % Keep only SQLite fields | |
| allFields = fieldnames(db_template('Subject', 'fields')); | |
| allValues = struct2cell(db_template('Subject', 'fields')); | |
| allFields = allFields(~strcmp(allValues, 'skip')); | |
| % Fields that are not copied from Default Anatomy | |
| noCopyFields = {'Id', 'Name', 'Comments', 'DateOfAcquisition', 'UseDefaultAnat', 'UseDefaultChannel'}; | |
| % Requested fields | |
| reqFields = allFields; | |
| % Prepare query | |
| joinQry = 'Subject LEFT JOIN Subject AS SubjectNoRaw ON Subject.UseDefaultAnat = 1 AND SubjectNoRaw.Id = 0'; | |
| fixFields = reqFields; | |
| for iField = 1 : length(reqFields) | |
| if ismember(reqFields{iField}, noCopyFields) | |
| fixFields{iField} = ['Subject.' reqFields{iField}]; | |
| else | |
| fixFields{iField} = ['COALESCE(SubjectNoRaw.' reqFields{iField} ', Subject.' reqFields{iField} ') AS "Subject.' reqFields{iField} '"']; | |
| end | |
| end | |
| addQuery = 'AND Subject.Id =6'; | |
| % NoRaw Subject | |
| % Using JOIN and COALESCE to get | |
| tic | |
| sSubject = sql_query('SELECT', joinQry, [], fixFields, addQuery); | |
| toc | |
| sSubject = struct_copy_fields(db_template('Subject'), sSubject); | |
| % Current approach (Fetch Subject, and check if copy is needed) | |
| tic | |
| sSubject2 = db_get('Subject', 6); | |
| toc | |
| isequal(sSubject2, sSubject) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment