Doctrine查询库，包含分组和最新记录

Question

I have a table "table_b" that contain the following details

I would like to use doctrine to query an output with a specific condition. Currently I'm using the block below to query.

$table_a= $em->getRepository('table_a')->findOneBy(['id'=>1]);
foreach($table_a->table_b as $records){
    echo $records->name. " : " . $records->value;
}

It will output the entire ArrayCollection. Is there a way to query the record base on latest 'Date Created', that is base on the grouping of column 'Foreign Key Table 2'.

Answer 1

If you want to use native Doctrine query methods, you should use findOneBy with the order byparameter.

findOneBy(['id' => 1], ['DateCreated' => 'desc'])

Then, you says the result is an ArrayCollection, so using the ArrayCollection::first() method, you'll get the last created element

EDIT

Imagine you have a Group entity and a Member entity. groups table is your table_a and members table is your table_b.

Entity description should be something like that :

class Group
{
    ...

    /**
     * @ORM\OneToMany(targetEntity="Group", mappedBy="member", cascade={"persist", "remove", "merge"})
     * @ORM\OrderBy({"dateCreated"="DESC"})
     */
    protected $members;

    ...

    public function __construct()
    {
        $members = new ArrayCollection();
    }

    // members handling accessors

    /**
     * @return ArrayCollection
     */
    public function getMembers()
    {
        return $this->members;
    }

    /**
     * @param $members
     *
     * @return $this
     */
    public function setMembers($members)
    {
        $this->members = new ArrayCollection();

        return $this->addMembers($members);
    }

    /**
     * @param $members
     *
     * @return $this
     */
    public function addMembers($members)
    {
        foreach ($members as $member)
        {
            $this->addMember($member);
        }

        return $this;
    }

    /**
     * @param Member $member
     *
     * @return $this
     */
    public function addMember(Member $member)
    {
        $this->members->add($member);
        $member->setGroup($this);

        return $this;
    }

    /**
     * @param Member $member
     *
     * @return $this
     */
    public function removeMember(Member $member)
    {
        if ($this->members->contains($member))
        {
            $this->members->removeElement($member);
        }

        return $this;
    }

    /**
     * @param $members
     *
     * @return $this
     */
    public function removeMembers($members)
    {
        foreach ($members as $member)
        {
            $this->removeMember($member);
        }

        return $this;
    }
}

And Member entity :

class Member
{
    /**
     * @ORM\ManyToOne(targetEntity="Group", inversedBy="members")
     * @ORM\JoinColumn(name="group_id", referencedColumnName="id", onDelete="CASCADE")
     */
    protected $group;

    /**
     * @ORM\Column(type="datetime", name="date_created")
     */
    protected $dateCreated;

    /**
     * @return Group
     */
    public function getGroup()
    {
        return $this->group;
    }

    /**
     * @param Group $group
     *
     * @return $this
     */
    public function setGroup(Group $group)
    {
        $this->group = $group;

        return $this;
    }
}

Now, we have one group with a dateCreated ordered collection of members.

Example 1 : You want to get the last member created for a given group

$group = $em->getRepository(Group::class)->findOneBy(['id' => 1]);
$lastMember = $group->getMembers()->first();

Example 2 : You want to get all members created on 2014-01-30 :

$members = $group->getMembers()->filter(function (Member $member) {
    return ($member->getDateCreated->format('Y-m-d') == '2014-01-30');
});

That's all folk !

PS : I haven't test this code