Laravel unable to run SQL query from a .sql file

Question

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);
   }

The error says your user lacks the SELECT privilege for at least one of the tables. Have you checked with SHOW GRANTS FOR 'moodleqa39'@'%' (or whatever your user is defined as)? — Bill Karwin
– Bill Karwin, Commented Sep 11, 2022 at 16:31
@BillKarwin There is no table called en in my db. However I don’t think I have access rights issue. I have tried simple commands like select * from mdl_establishements and it works fine — Karan
– Karan, Commented Sep 11, 2022 at 16:56

Joukhar · Accepted Answer · 2022-09-11 15:58:20Z

1

Try this

$fileData = Storage::disk('local')->get('/public/query.sql');
$jsonFileData = DB::raw($fileData);
var_dump($jsonFileData);

answered Sep 11, 2022 at 15:58

Joukhar

8921 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karan Over a year ago

This just gives me the SQL query as output

Karan · Accepted Answer · 2022-09-12 14:59:01Z

0

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);

edited Sep 12, 2022 at 14:59

answered Sep 12, 2022 at 14:38

Karan

2681 gold badge4 silver badges16 bronze badges

Collectives™ on Stack Overflow

Laravel unable to run SQL query from a .sql file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related