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

相关推荐 更多相似问题