3

How can I get non-mapped column in result from native query?

My query:

$query = $this->getEntityManager()->createNativeQuery(
            "SELECT m.id, m.title, MATCH(m.title) AGAINST('$slug') AS score "
            . "FROM music AS m "
            . "ORDER BY score DESC LIMIT 100", $rsm);

Column score isn't mapped in entity and I don't have access to its value from Twig. Is it possible to add this column into entity only for this query?

e1dar
  • 637
  • 1
  • 6
  • 13
MonkeyOne
  • 58
  • 1
  • 7
  • Did you try this? Does it work? Do you get an error? What is the error? – Wilt Jan 26 '16 at 08:28
  • query doesn't get error but when I try display score in Twig I got this: Method "score" for object "PlayerBundle\Entity\Music" does not exist in src\PlayerBundle\Resources\views\Default\search.html.twig at line 12 because score is not mapped in Entity class – MonkeyOne Jan 26 '16 at 09:32
  • I think you should just make sure your database model and object model correspond. Otherwise it will all become be very difficult and very buggy... – Wilt Jan 26 '16 at 09:34
  • I have field "rate" in "Music" Entity and I can overwrite value from score but I need both field. – MonkeyOne Jan 26 '16 at 09:42
  • Have you tried [this](http://stackoverflow.com/a/9833329/5759314)? – e1dar Jan 26 '16 at 09:48
  • I want select fields that are created within a SELECT statement, not update. The problem is that Doctrine get values only for mapped fields which are mapped in Entity. I don't know how to map "score" field only in this native query. – MonkeyOne Jan 26 '16 at 10:04

1 Answers1

2

If you want to show score inside Twig template, you can try following steps:

1) Add $score attribute without any mapping configuration to your Music entity:

class Music {
    //Other mappings

    protected $score;

    //TODO: add getter/setter for $score
}

2) Add it to your ResultSetMapper:

$rsm->addRootEntityFromClassMetadata('YourBundle:Music', 'm');
$rsm->addMetaResult('m', 'score', 'score', false, 'integer'); //first 'score' is your DB alias

3) Call in your search.html.twig:

{{ object.score }}

Where object is your Music entity.

Additional information about pure and mixed results could be found here.

e1dar
  • 637
  • 1
  • 6
  • 13
  • $rsm->addScalarResult have 3 parameters: ColumnName, alias, type. I use only 2 parameters(because third is optional) and get result: array:100 [▼ 0 => array:2 [▼ 0 => Music {#480 ▶} "score" => "12.595897674560547" ] 1 => array:2 [▼ 0 => Music {#561 ▶} "score" => "3.7423951625823975" ] ] score is outside Object – MonkeyOne Jan 26 '16 at 12:30
  • @MonkeyOne Yes, true. I have edited my answer and also tested it. Apparently this is the only way how to do what you want. – e1dar Jan 26 '16 at 14:10
  • I already have a "score" in the Music object but it's always NULL – MonkeyOne Jan 26 '16 at 17:36
  • @MonkeyOne Have you tried **addMetaResult** (you may also try to default 5th argument)? – e1dar Jan 26 '16 at 18:00
  • yes, I tried with and without 5th arguments and still return null. – MonkeyOne Jan 26 '16 at 18:05
  • 1
    I use $query->getArrayResult(); to get result from query – MonkeyOne Jan 26 '16 at 19:01