1

I need take the highest temperature form my read-out table. I try to convert this sql query to use with createQueryBuilder()

SQL: SELECT temperature_value FROM read_outs WHERE room_id = 3

And this work in PHPMyAdmin and i get result. Now i want to get only max value from all results.

I create function in repository:

public function getMaxTemperatureByRoom($id)
{
    $this->createQueryBuilder('read_out')
        ->select('read_out, MAX(read_out.temperatureValue) AS 
max_temperature')
        ->where('read_out.roomId = :id')
        ->setParameter('id', $id)
        ->getQuery()
        ->getResult();
}

This is my Entity Class:

 <?php

   namespace Smart\SensorBundle\Entity;

   use Doctrine\ORM\Mapping as ORM;
   use Smart\RoomBundle\Entity\Room;



/**
    * Class ReadOut
    * @package Smart\SensorBundle\Entity
    *
    * @ORM\Table(name="read_outs")
* 

@ORM\Entity(repositoryClass=
"Smart\SensorBundle\Repository\ReadOutReposi    tory")
 */
class ReadOut
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var Sensor
 *
 * @ORM\ManyToOne(targetEntity="Smart\SensorBundle\Entity\Sensor")
 * @ORM\JoinColumn(name="sensor_id", referencedColumnName="id", 
onDelete="CASCADE")
 */
private $sensor_id;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="read_date", type="date")
 */
private $readOutDate;

/**
 * @var float
 *
 * @ORM\Column(name="temperature_value", type="float")
 */
private $temperatureValue;

/**
 * @var float
 *
 * @ORM\Column(name="humidity_value", type="float")
 */
private $humidityValue;

/**
 * @var Room
 *
 * @ORM\ManyToOne(targetEntity="Smart\RoomBundle\Entity\Room")
 * @ORM\JoinColumn(name="room_id", referencedColumnName="id", 
onDelete="CASCADE")
 */
private $roomId;

/**
 * @return int
 */
public function getId(): int
{
    return $this->id;
}

/**
 * @param int $id
 */
public function setId(int $id): void
{
    $this->id = $id;
}

/**
 * @return Sensor
 */
public function getSensorId(): Sensor
{
    return $this->sensor_id;
}

/**
 * @param Sensor $sensor_id
 */
public function setSensorId(Sensor $sensor_id): void
{
    $this->sensor_id = $sensor_id;
}

/**
 * @return \DateTime
 */
public function getReadOutDate(): \DateTime
{
    return $this->readOutDate;
}

/**
 * @param \DateTime $readOutDate
 */
public function setReadOutDate(\DateTime $readOutDate): void
{
    $this->readOutDate = $readOutDate;
}

/**
 * @return float
 */
public function getTemperatureValue(): float
{
    return $this->temperatureValue;
}

/**
 * @param float $temperatureValue
 */
public function setTemperatureValue(float $temperatureValue): void
{
    $this->temperatureValue = $temperatureValue;
}

/**
 * @return float
 */
public function getHumidityValue(): float
{
    return $this->humidityValue;
}

/**
 * @param float $humidityValue
 */
public function setHumidityValue(float $humidityValue): void
{
    $this->humidityValue = $humidityValue;
}

/**
 * @return Room
 */
public function getRoomId(): Room
{
    return $this->roomId;
}

/**
 * @param Room $roomId
 */
public function setRoomId(Room $roomId): void
{
    $this->roomId = $roomId;
}
}

In this i get NULL:

$result = $this->getDoctrine()->getRepository(ReadOut::class)- 
>getMaxTemperatureByRoom(3);
    var_dump($result);die;

What i do wrong?

Thanks.

@EDIT

Now i get something like this:

array(2) { [0]=> object(Smart\SensorBundle\Entity\ReadOut)#3563 (6) { ["id":"Smart\SensorBundle\Entity\ReadOut":private]=> int(1) ["sensor_id":"Smart\SensorBundle\Entity\ReadOut":private]=> object(Proxies\__CG__\Smart\SensorBundle\Entity\Sensor)#6017 (14) { ["__initializer__"]=> object(Closure)#3555 (3) { ["static"]=> array(3) { ["entityPersister"]=> object(Doctrine\ORM\Persisters\Entity\BasicEntityPersister)#3529 (13) { ["class":protected]=> object(Doctrine\ORM\Mapping\ClassMetadata)#3524 (40) { ["name"]=> string(32) "Smart\SensorBundle\Entity\Sensor" ["namespace"]=> string(25) "Smart\SensorBundle\Entity" ["rootEntityName"]=> string(32) "Smart\SensorBundle\Entity\Sensor" ["customGeneratorDefinition"]=> NULL ["customRepositoryClassName"]=> string(46) "Smart\SensorBundle\Repository\SensorRepository" ["isMappedSuperclass"]=> bool(false) ["isEmbeddedClass"]=> bool(false) ["parentClasses"]=> array(0) { } ["subClasses"]=> array(0) { } ["embeddedClasses"]=> array(0) { } ["namedQueries"]=> array(0) { } ["namedNativeQueries"]=> array(0) { } ["sqlResultSetMappings"]=> array(0) { } ["identifier"]=> array(1) { [0]=> string(2) "id" } 

No ended result :(.

@EDIT2

It was problem with var_dump when i use dump i got correct response.

Thanks for help.

2
  • Tips, there is many things you improve following some standards: Ex:, why are you class variable suffixed with "value" ($temperatureValue) that's not needed, they don't have to be the same as the column name in DB (that's why you annotate), and you shouldn't suffix your related entity with "id", it is an object in Symfony not and ID: $item->getSensorId()->getSomething() doesn't make much sense and should be $item->getSensor()->getSomething() Also I don't see why you use the MAX sql functions since you specified the ID there will be only one value returned anyway. Cheers Commented Jan 3, 2019 at 14:31
  • Thanks for Tips :) But my result is still wrong :/ Commented Jan 3, 2019 at 14:40

1 Answer 1

5

You forget the return statement:

public function getMaxTemperatureByRoom($id)
{
    return $this->createQueryBuilder('read_out')
        ->select('read_out, MAX(read_out.temperatureValue) AS max_temperature')
        ->where('read_out.roomId = :id')
        ->setParameter('id', $id)
        ->getQuery()
        ->getResult()
    ;
}
Sign up to request clarification or add additional context in comments.

1 Comment

Ohh :D Now i get bug about ORDER_BY so i remove it form sql_mode. And i got a no ended result i edit question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.