如何在学说中编写联合查询?

2022-01-03 00:00:00 orm mysql doctrine doctrine-orm doctrine-query

我想在学说中使用联合，我搜索了很多但没有成功，这是我在sql中的联合查询，如何在学说中转换这个查询?

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

这是我的学说查询

    $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.

推荐答案

我已经找到了解决方案

我们可以将这个查询与 RSM 一起使用，如下所示

We can use this query with RSM as following

Usman 基本上就是表名和类"

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

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

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

相关文章