duanchui1279 2017-04-18 20:38
浏览 25
已采纳

在symfony的查询中组合两个变量

I'm having some trouble with what may be a simple question.

I need to be able to query two separate variables as a key using doctrine, but I'm not sure if I need to update the entity schema (I'd rather not, and it seems overkill) to be able to do this.

I'm currently needing to query a number of different items in a single DQL query in one field. I've been doing something like this:

        $qb->select('r')->from('Bundle:Reviews\Review', 'r')
        ->leftJoin('Bundle:Bibliographies\PersonalName', 'p', 'WITH', 'p.biblio = r.id')
        ->leftJoin('r.biblio', 'bib')
        ->where( 'bib.author LIKE ?1 OR p.surname LIKE ?1' );

so far so good. The problem I have is that unlike author in the Review entity, which includes both surname and first name, stored automatically like surname, givenName, on the PersonalName table (which exists as a one-to-many against Bibliography), there's no single field for both. I have surname, but what I really need is something like this:

   ->where( 'bib.author LIKE ?1 OR "p.surname", "p.givenName" LIKE ?1' );

which of course wont work.

To make this simpler, is there a way to do something like create a variable in the Repository that is a composite, define it as a parameter (e.g. $params[3] and then do something like this>

    ->where( 'bib.author LIKE ?1 OR ?3 LIKE ?1' );

Otherwise, I might be fine with doing it in the entity, but I'm not entirely sure how to do it.

The PersonalName entity has something like this:

    /**
 * @var string
 *
 * @ORM\Column(name="given_name", type="string", length=255, nullable=true)
 */
private $givenName;

/**
 * @var string
 *
 * @ORM\Column(name="surname", type="string", length=255, nullable=true)
 */
private $surname;

Is there an easy way of simply creating a new attribute that is a combination of surname and givenName without having to update the entity? I've been trying to find things in the manual but, alas no dice.

Any help appreciated.

Edit for clarification:

All I really need is in my where clause to give me something like this:

->where( 'bib.author LIKE ?1 OR (CONCAT(p.surname,CONCAT(", ",p.givenName)) LIKE ?1)' )

Unfortunately, where I was getting perfectly good results earlier searching on surname, or a substring of author, I am now getting zero results at all, which makes little sense to me, as there is a clear OR. I'm not sure where this is breaking the query.

  • 写回答

2条回答 默认 最新

  • duanshang3230 2017-04-19 18:06
    关注

    Okay so the advice I got above from @ccKep sent me in the right direction. However, apparently for some reason DQL does not like double quotes inside single quotes.

    This is the solution that worked for me:

    ->where( "bib.author LIKE ?1 OR CONCAT(p.surname,CONCAT(', ',p.givenName)) LIKE ?1" )
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 vhdl+MODELSIM
  • ¥20 simulink中怎么使用solve函数?
  • ¥30 dspbuilder中使用signalcompiler时报错Error during compilation: Fitter failed,求解决办法
  • ¥15 gwas 分析-数据质控之过滤稀有突变中出现的问题
  • ¥15 没有注册类 (异常来自 HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))
  • ¥15 知识蒸馏实战博客问题
  • ¥15 用PLC设计纸袋糊底机送料系统
  • ¥15 simulink仿真中dtc控制永磁同步电机如何控制开关频率
  • ¥15 用C语言输入方程怎么
  • ¥15 网站显示不安全连接问题