Laravel: Joining multiple select statements

Multi tool use
Multi tool use












0















I have a survey app.
Each SurveyResponse has multiple QuestionResponses.
For reporting purposes I want to filter the QuestionResponses based on values answered for other QuestionResponses that are part of the same SurveyResponse - eg. Give me all the question responses of those that selected Male for the Genderquestion.



The following example SQL query is working, but I am struggling to replicate it in Laravel:



SELECT * 
FROM ( SELECT *
FROM ( SELECT *
FROM question_responses ) AS A

JOIN ( SELECT survey_response_id AS JOIN_A
FROM question_responses
WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
ON A.survey_response_id= JOIN_A) AS C

JOIN ( SELECT survey_response_id AS JOIN_B
FROM question_responses
WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
ON C.survey_response_id=JOIN_B


How would I do this using Eloquent or Laravel's query builder?










share|improve this question























  • are you correct with those multiple selects ?

    – Leorent
    Jan 2 at 6:40











  • In laravel 5.7, you can use selectRaw() method to execute raw SQL queries. Ref

    – Vidhyut Pandya
    Jan 2 at 6:43


















0















I have a survey app.
Each SurveyResponse has multiple QuestionResponses.
For reporting purposes I want to filter the QuestionResponses based on values answered for other QuestionResponses that are part of the same SurveyResponse - eg. Give me all the question responses of those that selected Male for the Genderquestion.



The following example SQL query is working, but I am struggling to replicate it in Laravel:



SELECT * 
FROM ( SELECT *
FROM ( SELECT *
FROM question_responses ) AS A

JOIN ( SELECT survey_response_id AS JOIN_A
FROM question_responses
WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
ON A.survey_response_id= JOIN_A) AS C

JOIN ( SELECT survey_response_id AS JOIN_B
FROM question_responses
WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
ON C.survey_response_id=JOIN_B


How would I do this using Eloquent or Laravel's query builder?










share|improve this question























  • are you correct with those multiple selects ?

    – Leorent
    Jan 2 at 6:40











  • In laravel 5.7, you can use selectRaw() method to execute raw SQL queries. Ref

    – Vidhyut Pandya
    Jan 2 at 6:43
















0












0








0








I have a survey app.
Each SurveyResponse has multiple QuestionResponses.
For reporting purposes I want to filter the QuestionResponses based on values answered for other QuestionResponses that are part of the same SurveyResponse - eg. Give me all the question responses of those that selected Male for the Genderquestion.



The following example SQL query is working, but I am struggling to replicate it in Laravel:



SELECT * 
FROM ( SELECT *
FROM ( SELECT *
FROM question_responses ) AS A

JOIN ( SELECT survey_response_id AS JOIN_A
FROM question_responses
WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
ON A.survey_response_id= JOIN_A) AS C

JOIN ( SELECT survey_response_id AS JOIN_B
FROM question_responses
WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
ON C.survey_response_id=JOIN_B


How would I do this using Eloquent or Laravel's query builder?










share|improve this question














I have a survey app.
Each SurveyResponse has multiple QuestionResponses.
For reporting purposes I want to filter the QuestionResponses based on values answered for other QuestionResponses that are part of the same SurveyResponse - eg. Give me all the question responses of those that selected Male for the Genderquestion.



The following example SQL query is working, but I am struggling to replicate it in Laravel:



SELECT * 
FROM ( SELECT *
FROM ( SELECT *
FROM question_responses ) AS A

JOIN ( SELECT survey_response_id AS JOIN_A
FROM question_responses
WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
ON A.survey_response_id= JOIN_A) AS C

JOIN ( SELECT survey_response_id AS JOIN_B
FROM question_responses
WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
ON C.survey_response_id=JOIN_B


How would I do this using Eloquent or Laravel's query builder?







mysql laravel






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 2 at 6:33









HPageHPage

785919




785919













  • are you correct with those multiple selects ?

    – Leorent
    Jan 2 at 6:40











  • In laravel 5.7, you can use selectRaw() method to execute raw SQL queries. Ref

    – Vidhyut Pandya
    Jan 2 at 6:43





















  • are you correct with those multiple selects ?

    – Leorent
    Jan 2 at 6:40











  • In laravel 5.7, you can use selectRaw() method to execute raw SQL queries. Ref

    – Vidhyut Pandya
    Jan 2 at 6:43



















are you correct with those multiple selects ?

– Leorent
Jan 2 at 6:40





are you correct with those multiple selects ?

– Leorent
Jan 2 at 6:40













In laravel 5.7, you can use selectRaw() method to execute raw SQL queries. Ref

– Vidhyut Pandya
Jan 2 at 6:43







In laravel 5.7, you can use selectRaw() method to execute raw SQL queries. Ref

– Vidhyut Pandya
Jan 2 at 6:43














2 Answers
2






active

oldest

votes


















0














You can use the eloquent relationship, but if you want to use this particular type of query then you can use the DB class in laravel.



use IlluminateSupportFacadesDB;

$data = DB::select(DB::raw('SELECT *
FROM ( SELECT *
FROM ( SELECT *
FROM question_responses ) AS A

JOIN ( SELECT survey_response_id AS JOIN_A
FROM question_responses
WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
ON A.survey_response_id= JOIN_A.your_id) AS C

JOIN ( SELECT survey_response_id AS JOIN_B
FROM question_responses
WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
ON C.survey_response_id=JOIN_B.your_id'))


after getting the response you can retrieve the data by $data[0]['key'] for using '->' or making it collection you can use the Collection class.



use IlluminateSupportFacadesDB;
use IlluminateDatabaseEloquentCollection;

$tempData = Collection::make(DB::select(DB::raw('select query'));
($tempData[0])->key;





share|improve this answer


























  • quick dirty way :)

    – Leorent
    Jan 2 at 6:44



















0














You can use DB::select method to retrieve result.



use IlluminateSupportFacadesDB;


$results = DB::select("SELECT *
FROM ( SELECT *
FROM ( SELECT *
FROM question_responses ) AS A
JOIN ( SELECT survey_response_id AS JOIN_A
FROM question_responses
WHERE (question_short_name = ?) AND (value = ?)) AS B
ON A.survey_response_id= JOIN_A) AS C
JOIN ( SELECT survey_response_id AS JOIN_B
FROM question_responses
WHERE (question_short_name = ?) AND (value = ?)) AS D
ON C.survey_response_id= JOIN_B", ['Gender', 'Male', 'Age', '45 to 54']);

foreach ($results as $key => $row) {
print_r($row);
}





share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002189%2flaravel-joining-multiple-select-statements%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    You can use the eloquent relationship, but if you want to use this particular type of query then you can use the DB class in laravel.



    use IlluminateSupportFacadesDB;

    $data = DB::select(DB::raw('SELECT *
    FROM ( SELECT *
    FROM ( SELECT *
    FROM question_responses ) AS A

    JOIN ( SELECT survey_response_id AS JOIN_A
    FROM question_responses
    WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
    ON A.survey_response_id= JOIN_A.your_id) AS C

    JOIN ( SELECT survey_response_id AS JOIN_B
    FROM question_responses
    WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
    ON C.survey_response_id=JOIN_B.your_id'))


    after getting the response you can retrieve the data by $data[0]['key'] for using '->' or making it collection you can use the Collection class.



    use IlluminateSupportFacadesDB;
    use IlluminateDatabaseEloquentCollection;

    $tempData = Collection::make(DB::select(DB::raw('select query'));
    ($tempData[0])->key;





    share|improve this answer


























    • quick dirty way :)

      – Leorent
      Jan 2 at 6:44
















    0














    You can use the eloquent relationship, but if you want to use this particular type of query then you can use the DB class in laravel.



    use IlluminateSupportFacadesDB;

    $data = DB::select(DB::raw('SELECT *
    FROM ( SELECT *
    FROM ( SELECT *
    FROM question_responses ) AS A

    JOIN ( SELECT survey_response_id AS JOIN_A
    FROM question_responses
    WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
    ON A.survey_response_id= JOIN_A.your_id) AS C

    JOIN ( SELECT survey_response_id AS JOIN_B
    FROM question_responses
    WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
    ON C.survey_response_id=JOIN_B.your_id'))


    after getting the response you can retrieve the data by $data[0]['key'] for using '->' or making it collection you can use the Collection class.



    use IlluminateSupportFacadesDB;
    use IlluminateDatabaseEloquentCollection;

    $tempData = Collection::make(DB::select(DB::raw('select query'));
    ($tempData[0])->key;





    share|improve this answer


























    • quick dirty way :)

      – Leorent
      Jan 2 at 6:44














    0












    0








    0







    You can use the eloquent relationship, but if you want to use this particular type of query then you can use the DB class in laravel.



    use IlluminateSupportFacadesDB;

    $data = DB::select(DB::raw('SELECT *
    FROM ( SELECT *
    FROM ( SELECT *
    FROM question_responses ) AS A

    JOIN ( SELECT survey_response_id AS JOIN_A
    FROM question_responses
    WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
    ON A.survey_response_id= JOIN_A.your_id) AS C

    JOIN ( SELECT survey_response_id AS JOIN_B
    FROM question_responses
    WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
    ON C.survey_response_id=JOIN_B.your_id'))


    after getting the response you can retrieve the data by $data[0]['key'] for using '->' or making it collection you can use the Collection class.



    use IlluminateSupportFacadesDB;
    use IlluminateDatabaseEloquentCollection;

    $tempData = Collection::make(DB::select(DB::raw('select query'));
    ($tempData[0])->key;





    share|improve this answer















    You can use the eloquent relationship, but if you want to use this particular type of query then you can use the DB class in laravel.



    use IlluminateSupportFacadesDB;

    $data = DB::select(DB::raw('SELECT *
    FROM ( SELECT *
    FROM ( SELECT *
    FROM question_responses ) AS A

    JOIN ( SELECT survey_response_id AS JOIN_A
    FROM question_responses
    WHERE (question_short_name = 'Gender') AND (value = 'Male')) AS B
    ON A.survey_response_id= JOIN_A.your_id) AS C

    JOIN ( SELECT survey_response_id AS JOIN_B
    FROM question_responses
    WHERE (question_short_name = 'Age') AND (value = '45 to 54')) AS D
    ON C.survey_response_id=JOIN_B.your_id'))


    after getting the response you can retrieve the data by $data[0]['key'] for using '->' or making it collection you can use the Collection class.



    use IlluminateSupportFacadesDB;
    use IlluminateDatabaseEloquentCollection;

    $tempData = Collection::make(DB::select(DB::raw('select query'));
    ($tempData[0])->key;






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Jan 2 at 6:50

























    answered Jan 2 at 6:43









    Charanjeet SinghCharanjeet Singh

    1668




    1668













    • quick dirty way :)

      – Leorent
      Jan 2 at 6:44



















    • quick dirty way :)

      – Leorent
      Jan 2 at 6:44

















    quick dirty way :)

    – Leorent
    Jan 2 at 6:44





    quick dirty way :)

    – Leorent
    Jan 2 at 6:44













    0














    You can use DB::select method to retrieve result.



    use IlluminateSupportFacadesDB;


    $results = DB::select("SELECT *
    FROM ( SELECT *
    FROM ( SELECT *
    FROM question_responses ) AS A
    JOIN ( SELECT survey_response_id AS JOIN_A
    FROM question_responses
    WHERE (question_short_name = ?) AND (value = ?)) AS B
    ON A.survey_response_id= JOIN_A) AS C
    JOIN ( SELECT survey_response_id AS JOIN_B
    FROM question_responses
    WHERE (question_short_name = ?) AND (value = ?)) AS D
    ON C.survey_response_id= JOIN_B", ['Gender', 'Male', 'Age', '45 to 54']);

    foreach ($results as $key => $row) {
    print_r($row);
    }





    share|improve this answer




























      0














      You can use DB::select method to retrieve result.



      use IlluminateSupportFacadesDB;


      $results = DB::select("SELECT *
      FROM ( SELECT *
      FROM ( SELECT *
      FROM question_responses ) AS A
      JOIN ( SELECT survey_response_id AS JOIN_A
      FROM question_responses
      WHERE (question_short_name = ?) AND (value = ?)) AS B
      ON A.survey_response_id= JOIN_A) AS C
      JOIN ( SELECT survey_response_id AS JOIN_B
      FROM question_responses
      WHERE (question_short_name = ?) AND (value = ?)) AS D
      ON C.survey_response_id= JOIN_B", ['Gender', 'Male', 'Age', '45 to 54']);

      foreach ($results as $key => $row) {
      print_r($row);
      }





      share|improve this answer


























        0












        0








        0







        You can use DB::select method to retrieve result.



        use IlluminateSupportFacadesDB;


        $results = DB::select("SELECT *
        FROM ( SELECT *
        FROM ( SELECT *
        FROM question_responses ) AS A
        JOIN ( SELECT survey_response_id AS JOIN_A
        FROM question_responses
        WHERE (question_short_name = ?) AND (value = ?)) AS B
        ON A.survey_response_id= JOIN_A) AS C
        JOIN ( SELECT survey_response_id AS JOIN_B
        FROM question_responses
        WHERE (question_short_name = ?) AND (value = ?)) AS D
        ON C.survey_response_id= JOIN_B", ['Gender', 'Male', 'Age', '45 to 54']);

        foreach ($results as $key => $row) {
        print_r($row);
        }





        share|improve this answer













        You can use DB::select method to retrieve result.



        use IlluminateSupportFacadesDB;


        $results = DB::select("SELECT *
        FROM ( SELECT *
        FROM ( SELECT *
        FROM question_responses ) AS A
        JOIN ( SELECT survey_response_id AS JOIN_A
        FROM question_responses
        WHERE (question_short_name = ?) AND (value = ?)) AS B
        ON A.survey_response_id= JOIN_A) AS C
        JOIN ( SELECT survey_response_id AS JOIN_B
        FROM question_responses
        WHERE (question_short_name = ?) AND (value = ?)) AS D
        ON C.survey_response_id= JOIN_B", ['Gender', 'Male', 'Age', '45 to 54']);

        foreach ($results as $key => $row) {
        print_r($row);
        }






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 2 at 6:54









        JaysonJayson

        904624




        904624






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002189%2flaravel-joining-multiple-select-statements%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Bnj6chtQr f hTYeb8jrCXqnxKTI2Y7W,nR1FnP
            dZQgR82KEKBd77As,jkmluhsq3NLqkr 1y

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas