Entity relation OneToMany get onlye one record.. but many expected

Question

I have some problems with 2 tables relation with Symfony

NucleiStastiche1M contain time-series data of Nuclei..

One Nuclei can have many rows oof NucleiStatistiche1M

Nuclei SQL

CREATE TABLE public.nuclei
(
    id integer NOT NULL,
    nome character varying,
    cognome character varying
)

NucleiStatistiche1M SQL

CREATE TABLE public.nucleistatistiche1m
(
    id integer NOT NULL,
    dataora timestamp NOT NULL,
    value1 character varying
    value2 character varying
)

I defined the relationship on Symfony so

Nuclei.php

/**
 * @var NucleiStatistiche1M
 *
 * @ORM\OneToMany(targetEntity="NucleiStatistiche1M", mappedBy="nucleo")
 * @ORM\JoinColumn(name="id", referencedColumnName="id")
 *
 */
private $statistiche1M;

public function __construct()
{
    $this->statistiche1M = new ArrayCollection();
}

/**
 * @return Collection|NucleiStatistiche1M[]
 */
public function getStatistiche1M(): Collection
{
    return $this->statistiche1M;
}

NucleStatistiche1M.php

/**
* @var Nuclei
*
* @ORM\ManyToOne(targetEntity="Nuclei", inversedBy="statistiche1M")
* @ORM\JoinColumn(name="id", referencedColumnName="id")
*
*/
private $nucleo;

When i join the 2 tables i only get 1 record.. I do something like

    $qb = $em->createQueryBuilder()
        ->select('n, t, u, ns, ns1m, ts')
        ->from('App\Entity\Nuclei','n')
        ->leftJoin('n.statistiche1M', 'ns1m');

I expect many record i don't undestand where is the problem, the only problema i think is the joined table have same value on ID

thanks

What are you doing with $qb? We are missing too much of your code to get a clear idea. But I'm pretty sure the problem here is your mindset. I think your thinking in terms of table rows while retrieving objects. It's a different paradigm. — Julien B., Dec 18 '20 at 22:13
Nothing particular, getResults from query and pass them to the view. Let me know what details i edit answer — Augusto Murri, Dec 18 '20 at 22:19
I expect to have a collection of objects into $nucleo->statistiche1M :/ — Augusto Murri, Dec 18 '20 at 22:27
I will update the answer with more code details and table data thanks — Augusto Murri, Dec 18 '20 at 22:30

Julien B. · Answer 1 · 2020-12-18T22:41:01.307

0

You are retrieving objects, not table rows. This misunderstanding is very common when moving from plain SQL to an ORM.

To get the different related objects (in this case Statistiche1M) you would normally use a getter.

$nuclei->getStatistiche1M()

Which should return a collection of Statistiche1M objects.

In Twig you could iterate through the related object with a for loop for example.

{% for statistiche1M in nuclei.statistiche1M %}
 {{ statistiche1M.propertyToDisplay }}
{% endfor %}

One reason why you would use an explicit join in your query is to prefetch the related objects in a single query regardless of the fetch mode set on the entity.

When using LAZY fetching, a call to $nuclei->getStatistiche1M() would trigger another query to the database which could affect performance.

This answer explains very well the difference between the different fetch modes.

https://stackoverflow.com/a/26895601

edited Dec 18 '20 at 22:41

answered Dec 18 '20 at 22:37

Julien B.

3,023
2
18
33

I made this exactly, i only get one record instead of many.. I have a getter method and use it into a loop to fetch data – Augusto Murri Dec 18 '20 at 22:40
Maybe update your question with code showing how you are using the objects and where the problem really is. I could have misunderstood your problem. – Julien B. Dec 18 '20 at 22:45
Ok, understand the problem.. :( The joined table should have UNIQUE ids value, in my case the column "id" assume the same value repeated. I created a new column something like "ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;" .. updated the entity.. and now the join return many records – Augusto Murri Dec 21 '20 at 16:09

Entity relation OneToMany get onlye one record.. but many expected

1 Answers1