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