dongtuo0828
dongtuo0828
2017-05-10 05:30

当每个用户有多行时，使用Doctrine获取db中的最后一个用户条目

已采纳

I am trying to get the last entry made in the database for the user in order to make calculations with the data in my program. Using the last() method does not work in this instance as last() will return a null value. Please read on and I'll explain further. Check out my controller here: 

/**
 *
 * @Route("user/PyramidPressOverHead", name="user_PyramidPressOverHead")
 * @param Request $request
 * @return \Symfony\Component\HttpFoundation\Response
 *
 */
public function pyramidPressOverHead(Request $request)
{
    $exercisestats = $this->getExerciseStatsByCurrentUser();
    $newwtpoh15 = $this->getExerciseWeightAndReps('poh1',15);
    $newwtpoh12 = $this->getExerciseWeightAndReps('poh2',12);
    $newwtpoh10 = $this->getExerciseWeightAndReps('poh3',10);
    $newwtpoh8 = $this->getExerciseWeightAndReps('poh4',8);
    $newwtpoh6 = $this->getExerciseWeightAndReps('poh5',6);

    $form = $this->createForm('AppBundle\Form\PressOverHeadStatsType', $exercisestats);
    $form->handleRequest($request);

    if ($form->isValid()) {
        $em = $this->getDoctrine()->getManager();
        $user = $this->getUser();
        $exercisestats->setUserId($this->getUser());
        $exercisestats->setTimestamp(date('Y-m-d H:i:s'));
        $em->persist($exercisestats);
        $em->persist($user);
        $em->flush();

        return $this->redirectToRoute('user_PyramidTricepsExtension');
    }

    return $this->render('user/PyramidPressOverHead.html.twig', array(
        'exercisestats' => $exercisestats,
        'form' => $form->createView(),
        'poh15' => $newwtpoh15, 'poh12' => $newwtpoh12, 'poh10' => $newwtpoh10,
        'poh8' => $newwtpoh8, 'poh6' => $newwtpoh6,
    ));
}

The controller doesn't really show you what is commited. The twig gives you more here: 

{% extends 'base.html.twig' %}
{% block body %}
<head>


    {% block stylesheets %}<link href="{{ asset('css/custom.css') }}" 
    rel="stylesheet" media="screen">{% endblock %}
    </head>
<body background="{{ asset('sport-1244925_1920.jpg') }}">

{{ form_start(form, { 'style': 'horizontal', 'col_size': 'xs' }) }}
<h1>Press Over Head</h1>
<h3>Your Weight for Set One is {{ poh15 }}</h3>
{{ form_row(form.press_over_head_1_reps) }}
{{ form_row(form.press_over_head_1_weight) }}
<h3>Your Weight for Set two is {{ poh12 }}</h3>
{{ form_row(form.press_over_head_2_reps) }}
{{ form_row(form.press_over_head_2_weight) }}
<h3>Your Weight for Set three is {{ poh10 }}</h3>
{{ form_row(form.press_over_head_3_reps) }}
{{ form_row(form.press_over_head_3_weight) }}
<h3>Your Weight for Set four is {{ poh8 }}</h3>
{{ form_row(form.press_over_head_4_reps) }}
{{ form_row(form.press_over_head_4_weight) }}
<h3>Your Weight for Set five is {{ poh6 }}</h3>
{{ form_row(form.press_over_head_5_reps) }}
{{ form_row(form.press_over_head_5_weight) }}

<h2>Before hitting the submit button, please check over all of your
numbers to ensure they are accurate.</h2>
{{ form_row(form.submit) }}


{% endblock %}

The person enters in how many reps they did as well as the weight for each exercise. When they hit the submit button, it commits to the db. The table has about 40 columns in it. It is populated with the user's stats one exercise at a time. When one exercise is committed, those columns which have no data in them are made null when the commit is done. Here's a snippet of my entity: 

/**
 * @var int
 * @ORM\Column(name="press_over_head_1_reps", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_1_reps;

/**
 * @var int
 * @ORM\Column(name="press_over_head_1_weight", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_1_weight;

/**
 * @var int
 * @ORM\Column(name="press_over_head_2_reps", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_2_reps;

/**
 * @var int
 * @ORM\Column(name="press_over_head_2_weight", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_2_weight;

/**
 * @var int
 * @ORM\Column(name="press_over_head_3_reps", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_3_reps;

/**
 * @var int
 * @ORM\Column(name="press_over_head_3_weight", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_3_weight;

/**
 * @var int
 * @ORM\Column(name="press_over_head_4_reps", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_4_reps;

/**
 * @var int
 * @ORM\Column(name="press_over_head_4_weight", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_4_weight;

/**
 * @var int
 * @ORM\Column(name="press_over_head_5_reps", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_5_reps;

/**
 * @var int
 * @ORM\Column(name="press_over_head_5_weight", type="smallint", length=6,
 *     nullable=true)
 */
private $press_over_head_5_weight;

I have everything set in the entity to nullable=true because if a commit is done without nullable=true in the fields that don't get data yet, errors are thrown.

The program tracks the person's statistics, so each time they work out, there is a new row for that workout day. So, in trying to use this in my controller:

$this->getUser()->getExerciseStats()->last()->getPressOverHead2Reps();

it returns null. Press Over Head is the 2nd exercise in the routine. Since the first exercise has been submitted, it rendered all blank fields with null, hence every exercise after the first one returns null, but I need the last actual entry in the db to make calculations and populate the twig.

Here's my User entity: 

/**
 * @ORM\OneToMany(targetEntity="AppBundle\Entity\ExerciseStats", 
mappedBy="user_id")
 */
private $exerciseStats;

/**
 * @return mixed
 */
public function getExerciseStats()
{
    return $this->exerciseStats;
}

Does anyone have any ideas of how I could get the data for that last row entered by the user in the db?

Let me know if anyone needs any clarification. Thanks for taking the time to check this out.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dtttlua7165 dtttlua7165 4年前

    I have solved this issue with a dql query, returning exactly what I am looking for: 

    public function getStatsByDQL(User $user,  String $col)
{

$midNight = date_create('00:00:00')->format('Y-m-d H:i:s');
$parameters = (array('user' => $user, 'date' => $midNight, 

));
$em2 = $this->getDoctrine()->getManager()
->getRepository('AppBundle:ExerciseStats')
    ->createQueryBuilder('g')
    ->setParameters($parameters)
    ->where('g.user = :user', 'g.timestamp < :date')
    ->select('g.'. $col)
    ->setMaxResults(1)
    ->join('g.user', 'user')
    ->orderBy( 'g.'. $col,'DESC')
    ->getQuery()->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
    return $em2;
}
    点赞 评论 复制链接分享
提交

为你推荐