madhur madhur - 2 months ago 10
MySQL Question

getting a column value from table as keys in result from sql query

I have a table structure as -

name grade
a 10
b 8
c 9


I am writing a query for selecting rows where grade is more than 7, and want the result in this format

{
a: {grade: 10},
b: {grade: 8},
c: {grade: 9}
}


here is my query

createQueryBuilder()
->select('x.name, x.grade as grade')
->from('gradesTable', 'x')
->where('x.grade > :threshold')
->setParameter('threshold', 7)
->getQuery()
->getResult();


Can someone help with this?

Answer

The 'from' function has a third parameter called indexBy. Tha is what you are looking for. Your query should look something like this.

....->createQueryBuilder()
    ->select('x.name, x.grade as grade')
    ->from('gradesTable', 'x', 'x.name')
    ->where('x.grade > :threshold')
    ->setParameter('threshold', 7)
    ->getQuery()
    ->getResult();

And the result will be something like this

{
  a: {name: a, grade: 10},
  b: {name: b, grade: 8},
  c: {name: c, grade: 9}
}

Don't remove the name from the query. It will not work if you don't select it. Hope this helps.

Happy coding,

Alexandru Cosoi