如何在学说中写联合查询？ [英] How to write union query in doctrine?

问题描述

我想在doctrine中使用union，我搜索了很多但没有得到任何成功，这是我在sql中的联合查询，如何在doctrine中转换这个查询？

$ b $选择*从（select orderid，tutorialId，points，allow_multiple，question，answer1，image1，correct1，answer2，image2，correct2，answer3，image3，correct3，answer4，image4，作为媒体，as作为内容的正确4，答案5，5，1，作为media_test的media_type_id

union

select orderid， tutorialId，'0'as istest，content，media，media_type_id，''as points，''as allow_multiple，''as question，''as answer1，''as image1，''as correct1''as answer2，' as2，as，as，as3，as，as，as，4， 'as correct5 from tutorial_elements）a where a。 tutorialId = 1 order by orderid asc

这是我的教义查询

  $ query =SELECT * FROM（SELECT 
 tt.tutorialid 
 FROM 
 TutorialTest tt 
 UNION 
 SELECT te.tutorialid）tte 
 WHERE tte.tutorialid = 1 
 
; 
 $ qb = $ this-> Doctrine-> createQuery（$ query）; 
 $ tutorial_test = $ qb-> getResult（）; 
  

我研究了很多，但没有取得任何成功，如果有人可以帮助，百万感谢

解决方案

我找到了一个解决方案

我们可以使用这个查询与RSM如下

Usman基本是表名和类

  $ rsm = new ResultSetMapping（）; 
 $ rsm-> addEntityResult（'Usmans'，'u'）; 
 $ rsm-> addFieldResult（'u'，'orderid'，'orderid'）; 
 $ rsm-> addFieldResult（'u'，'tutorialId'，'tutorialid'）; 
 $ rsm-> addFieldResult（'u'，'points'，'points'）; 
 
 $ query = $ this-> Doctrine-> createNativeQuery（'SELECT * FROM usman'，$ rsm）; 
 $ tutorial_tests = $ query-> getResult（）; 
  

我们可以在没有ORM的情况下使用

  $ testQuery =
 select * from（
 select orderid，
 tutorialId，
 points，
 allow_multiple，
问题，
答案1，
 image1，
正确1，
答案2，
 image2，
 correct2，
答案3，
 image3 ，
 correct3，
 answer4，
 image4，
 correct4，
 answer5，
 image5，
 correct5，
'1'作为测试，
作为内容，
作为媒体，
作为media_type_id 
从tutorial_test 
 
联合
 
选择orderid，
 tutorialId，
'0'as istest，
 content，
 media，
 media_type_id，
点，
为allow_multiple，
为问题，
为answer1，
为image1，
为正确1，
''作为answer2，
''as image2，
''as correct2，
''as answer3，
''as image3，
''as correct3 ，
''as answer4，
''as image4，
''as correct4，
''as answer5，
''as image5，
 ''as correct5 
 from tutorial_elements 
）a 
其中a。 tutorialId = $ tutorial_id 
 order by orderid asc 
; 
 
 $ resultSets = $ this-> Doctrine-> getConnection（） - > fetchAll（$ testQuery）; 
  

I want to use union in doctrine, i searched a lot but didn't get any success, this is my union query in sql, how to convert this query in doctrine?

select * from (select orderid,tutorialId,points,allow_multiple,question,answer1,image1,correct1,answer2,image2,correct2,answer3,image3,correct3,answer4,image4,correct4,answer5,image5,correct5,'1' as istest,'' as content,'' as media,'' as media_type_id from tutorial_test

union

select orderid,tutorialId,'0' as istest,content,media,media_type_id,'' as points,'' as allow_multiple,'' as question,'' as answer1,'' as image1,'' as correct1,'' as answer2,'' as image2,'' as correct2,'' as answer3,'' as image3,'' as correct3,'' as answer4,'' as image4,'' as correct4,'' as answer5,'' as image5,'' as correct5  from tutorial_elements) a where a. tutorialId = 1 order by orderid asc

AND this one is my doctrine query

    $query  = "SELECT * FROM(SELECT
                    tt.tutorialid
                FROM
                    TutorialTest tt
                UNION 
                SELECT te.tutorialid) tte
                WHERE tte.tutorialid = 1    

    ";
    $qb     = $this->Doctrine->createQuery($query);
    $tutorial_test  = $qb->getResult();

i researched alot but didn't get any success, if any one can help, million of thanks in advance fot that.

解决方案

Well i have found a solution

We can use this query with RSM as following

"Usman is basically table name and class"

    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('Usmans', 'u');
    $rsm->addFieldResult('u', 'orderid', 'orderid');
    $rsm->addFieldResult('u', 'tutorialId', 'tutorialid');
    $rsm->addFieldResult('u', 'points', 'points');

    $query = $this->Doctrine->createNativeQuery('SELECT * FROM usman', $rsm); 
    $tutorial_tests = $query->getResult();

AND we can use without ORM as

$testQuery = "
    select * from (
            select orderid, 
                tutorialId, 
                points, 
                allow_multiple, 
                question, 
                answer1, 
                image1, 
                correct1, 
                answer2, 
                image2, 
                correct2, 
                answer3, 
                image3, 
                correct3, 
                answer4, 
                image4, 
                correct4, 
                answer5, 
                image5, 
                correct5, 
                '1' as istest, 
                '' as content, 
                '' as media, 
                '' as media_type_id 
            from tutorial_test

            union

            select orderid, 
                tutorialId, 
                '0' as istest, 
                content, 
                media, 
                media_type_id, 
                '' as points, 
                '' as allow_multiple, 
                '' as question, 
                '' as answer1, 
                '' as image1,
                '' as correct1,
                '' as answer2,
                '' as image2,
                '' as correct2,
                '' as answer3,
                '' as image3,
                '' as correct3,
                '' as answer4,
                '' as image4,
                '' as correct4,
                '' as answer5,
                '' as image5,
                '' as correct5  
            from tutorial_elements
        ) a 
        where a. tutorialId = $tutorial_id 
        order by orderid asc
";

$resultSets = $this->Doctrine->getConnection()->fetchAll($testQuery);

这篇关于如何在学说中写联合查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！