I am trying to run an SQL query provided to me in a .sql file. The query is as below:
SELECT CONCAT('[', GROUP_CONCAT(json_column SEPARATOR ','),']') AS jsonout
FROM
(SELECT JSON_OBJECT(
'courseid', (CAST(c.id AS JSON)),
'title.en',CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c.fullname, '{mlang en}', -1), '{mlang}', 1) AS CHAR(255)),
'title.fr',CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c.fullname, '{mlang fr_ca}', -1), '{mlang}', 1) AS CHAR(255)),
'shortname.en', CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c.shortname, '{mlang en}', -1), '{mlang}', 1) AS CHAR(255)) ,
'shortname.fr', CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c.shortname, '{mlang fr_ca}', -1), '{mlang}', 1) AS CHAR(255)),
'idnumber', c.idnumber ,
'idnumberalt', SUBSTRING(c.idnumber, 1, 4) ,
'establishmentaltname', e.altname,
'establishmentfullname', e.fullname,
'parentestablishmentshortname',(CASE ep.shortname WHEN 'RSSS' THEN 'INSPQ' ELSE ep.shortname END) ,
'parentestablishmentfullname' ,(CASE ep.fullname WHEN 'Réseau de la santé et des services sociaux' THEN 'INSPQ' ELSE ep.fullname END) ,
'duree', midduredelaformation.data ,
'dureeminutes',CAST(SUBSTRING_INDEX(midduredelaformation.data, 'h', 1) AS SIGNED) * 60 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(midduredelaformation.data, 'h', -1), 'm', 1) AS SIGNED),
'keywords',(SELECT CAST(CONCAT("[",GROUP_CONCAT(CONCAT('"',NAME,'"')),"]") AS JSON) keywords
FROM mdl_tag
WHERE id IN
(SELECT tagid
FROM mdl_tag_instance
WHERE itemid = c.id)
GROUP BY tagcollid),
'ispartageable',IF(midshare.data = 1, CAST(TRUE AS JSON), CAST(FALSE AS JSON)) ,
'partageable',(CASE midshare.data WHEN 1 THEN 'Partageable' ELSE 'Locale' END) ,
'visible',IF(c.visible = 1, CAST(TRUE AS JSON), CAST(FALSE AS JSON)) ,
'category.en', CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ccat.name, '{mlang en}', -1), '{mlang}', 1) AS CHAR(255)) ,
'category.fr', CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ccat.name, '{mlang fr_ca}', -1), '{mlang}', 1) AS CHAR(255)) ,
'modalite.fr',(CASE midmodalite.data
WHEN 0 THEN 'En présentiel'
WHEN 1 THEN 'En ligne'
WHEN 2 THEN 'En ligne'
WHEN 3 THEN 'Hybride (En présentiel en ligne)'
END) ,
'modalite.en',(CASE midmodalite.data
WHEN 0 THEN 'In Person'
WHEN 1 THEN 'Online'
WHEN 2 THEN 'Online'
WHEN 3 THEN 'Hybrid (In person Online)'
END) ,
'clientele.fr',(
SELECT CAST(CONCAT("[",GROUP_CONCAT(CONCAT('"',valor,'"')),"]") AS JSON) keywords
FROM
(
SELECT IF(POSITION('"value1":"1"' IN mcmclientele.data) > 1,'Personnel en soins infirmiers et cardio-respiratoire','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Personnel en soins infirmiers et cardio-respiratoire'
UNION ALL
SELECT IF(POSITION('"value2":"1"' IN mcmclientele.data) > 1,'Personnel paratechnique, service auxiliaire et métier','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Personnel paratechnique, service auxiliaire et métier'
UNION ALL
SELECT IF(POSITION('"value3":"1"' IN mcmclientele.data) > 1,'Personnel de bureau, technicien et professionnel de l\'administration','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Personnel de bureau, technicien et professionnel de l\'administration'
UNION ALL
SELECT IF(POSITION('"value4":"1"' IN mcmclientele.data) > 1,'Technicien et
professionnel de la santé et des services sociaux','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Technicien et professionnel de la santé et des services sociaux'
UNION ALL
SELECT IF(POSITION('"value5":"1"' IN mcmclientele.data) > 1,'Personnel d\'encadrement','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Personnel d\'encadrement'
UNION ALL
SELECT IF(POSITION('"value6":"1"' IN mcmclientele.data) > 1,'Autre','') AS valor,
mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Autre'
)
AS tablas
GROUP BY courseid
HAVING courseid = c.id
),
'clientele.en',(
SELECT CAST(CONCAT("[",GROUP_CONCAT(CONCAT('"',valor,'"')),"]") AS JSON) 'client.en'
FROM
(
SELECT IF(POSITION('"value1":"1"' IN mcmclientele.data) > 1,'Nursing and cardiorespiratory personnel','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Nursing and cardiorespiratory personnel'
UNION ALL
SELECT IF(POSITION('"value2":"1"' IN mcmclientele.data) > 1,'Paratechnical staff, auxiliary service staff and trades','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Paratechnical staff, auxiliary service staff and trades'
UNION ALL
SELECT IF(POSITION('"value3":"1"' IN mcmclientele.data) > 1,'Office staff, administrative technician and professional','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Office staff, administrative technician and professional'
UNION ALL
SELECT IF(POSITION('"value4":"1"' IN mcmclientele.data) > 1,'Health and social services technicians and professionals','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Health and social services technicians and professionals'
UNION ALL
SELECT IF(POSITION('"value5":"1"' IN mcmclientele.data) > 1,'Management staff','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Management staff'
UNION ALL
SELECT IF(POSITION('"value6":"1"' IN mcmclientele.data) > 1,'Other','') AS valor, mcmclientele.courseid
FROM mdl_course_meta_info_data AS mcmclientele
WHERE mcmclientele.fieldid = 1
GROUP BY 1,2
HAVING valor = 'Other'
) AS tablas
GROUP BY courseid
HAVING courseid = c.id
),
'lastupdate',CAST(middatedeconception.data AS JSON) ,
'accreditation',IF(midaccreditation.data LIKE '%"checkboxvalue":1%', CAST(TRUE AS JSON), CAST(FALSE AS JSON)) ,
'summary.en',CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c.summary, '{mlang en}', -1), '{mlang}', 1) AS CHAR(255)) ,
'summary.fr',CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c.summary, '{mlang fr_ca}', -1), '{mlang}', 1) AS CHAR(255)),
"fcpresponsable",(CASE midfcpresponsable.data
WHEN 1 THEN "Institut national de santé publique du Québec"
WHEN 2 THEN "Centre universitaire de santé McGill"
WHEN 3 THEN "Centre hospitalier de l’Université de Montréal"
WHEN 4 THEN "CHU de Québec – Université Laval"
WHEN 5 THEN "CIUSSS de l’Estrie – Centre hospitalier universitaire de Sherbrooke"
ELSE ""
END) ,
'nombreinscriptions',(SELECT CAST(COUNT(courseid) AS JSON)
FROM mdl_user_enrolments AS ue INNER JOIN
mdl_enrol AS en ON en.id = enrolid
WHERE en.courseid = c.id) ,
'imgurl',(SELECT CONCAT('/pluginfile.php/', ctx.id, '/course/overviewfiles/', f.filename) AS imgurl
FROM mdl_files f, mdl_context ctx
WHERE f.contextid = ctx.id
AND ctx.instanceid = c.id
AND ctx.contextlevel = 50
AND f.component = 'course'
AND f.filearea = 'overviewfiles'
AND f.filesize > 0 )
) AS json_column
FROM
mdl_course AS c
INNER JOIN mdl_course_categories AS ccat ON c.category = ccat.id
INNER JOIN mdl_establishment_course AS ec ON ec.courseid = c.id
INNER JOIN mdl_establishment e ON e.id = ec.establishmentid
INNER JOIN mdl_establishment ep ON e.parentid = ep.id
INNER JOIN mdl_course_meta_info_data AS midshare ON c.id = midshare.courseid AND midshare.fieldid = 35
INNER JOIN mdl_course_meta_info_data AS midmodalite ON midmodalite.courseid = c.id AND midmodalite.fieldid = 4
INNER JOIN mdl_course_meta_info_data AS middatedeconception ON c.id = middatedeconception.courseid AND middatedeconception.fieldid = 2
INNER JOIN mdl_course_meta_info_data AS midduredelaformation ON midduredelaformation.courseid = c.id AND midduredelaformation.fieldid = 3
INNER JOIN mdl_course_meta_info_data AS midclientele ON midclientele.courseid = c.id AND midclientele.fieldid = 1
INNER JOIN mdl_course_meta_info_data AS midaccreditation ON midaccreditation.courseid = c.id AND midaccreditation.fieldid = 8
LEFT JOIN mdl_course_meta_info_data AS midfcpresponsable ON midfcpresponsable.courseid = c.id AND midfcpresponsable.fieldid = 37
WHERE c.id > 1 AND c.category NOT IN (33)
AND c.id > 1
ORDER BY c.id DESC, ep.fullname, e.fullname, c.fullname limit 300)t
GROUP BY NULL;
I have validated the query with https://www.eversql.com/ and it also successfully runs in MySql/PHPMyAdmin providing the desired result. However when I run this query with Laravel I get an error stating missing quotation mark at position 1025. I have tried converting the query into eloquent using the tool https://jjlabajo.github.io/SQLtoEloquent/ but that doesn't work as well. Below is my snippet of code first trying to run the query from a file
public function store() {
$fileData = Storage::disk('local')->get('/public/query.sql');
$jsonFileData = DB::select($fileData);
var_dump($jsonFileData);
}
Here is what I tried with eloquent which gave me an error Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'moodleqa39'@'172.30.161.201' for table 'en'
below is the code:
public function store() {
$fileData = Storage::disk('local')->get('/public/query.sql');
$jsonFileData = DB::table("mdl_course_meta_info_data as mcmclientele")
->select("if (position(''value2':'1'' in mcmclientele.data) > 1", "'personnel paratechnique", "service auxiliaire et métier'", "'') as valor", "mcmclientele.courseid")
->where("mcmclientele.fieldid", "=", 1)
->groupBy("1");
DB::table("where_subquery_group_2_), 'title.en', cast where_subquery_group_3_, '{mlang}', 1) as char where_subquery_group_4_), 'title.fr', cast where_subquery_group_5_, '{mlang}', 1) as char where_subquery_group_6_), 'shortname.en', cast where_subquery_group_7_, '{mlang}', 1) as char where_subquery_group_8_) , 'shortname.fr', cast where_subquery_group_9_, '{mlang}', 1) as char where_subquery_group_10_), 'idnumber', c.idnumber , 'idnumberalt', substring where_subquery_group_11_ , 'establishmentaltname', e.altname, 'establishmentfullname', e.fullname, 'parentestablishmentshortname', where_subquery_group_12_ , 'parentestablishmentfullname' , where_subquery_group_13_ , 'duree', midduredelaformation.data , 'dureeminutes', cast where_subquery_group_14_ as signed) * 60 cast where_subquery_group_15_, 'm', 1) as signed), 'keywords', where_subquery_group_16_), ']') as json) keywords")
->select("concat ('[', group_concat(json_column separator ', ')", "']') as jsonout")
->where(DB::raw("mdl_tag"))
->union($jsonFileData)->groupBy("tagcollid")
->get();
var_dump($jsonFileData);
}
CodePudding user response：
Try this
$fileData = Storage::disk('local')->get('/public/query.sql');
$jsonFileData = DB::raw($fileData);
var_dump($jsonFileData);
CodePudding user response：
Found the fix, you have to set SET group_concat_max_len = 1000000; first. So in my code I did:
$fileData = Storage::disk('local')->get('/public/query.sql');
DB::statement('SET group_concat_max_len = 1000000');
$jsonFileData = DB::select( DB::raw("$fileData"));
var_dump($jsonFileData[0]->jsonout);