Fetching data from Symfony Doctrine DB

Question

I have just started my first symfony project and im a little bit confused.

I would like to query database for Shelfs which have no products on them. I have come up with such sql query which seems to work:

select distinct she.id shelf_id,rac.id rack_id , row.id row_id, war.id warehouse_id from shelfs she
left join products pro on she.id = pro.warehouse_id
left join racks rac on she.rack_id=rac.id
left join rows row on rac.row_id = row.id
left join warehouses war on row.warehouse_id = war.id
where she.id not in (select shelf_id from products);

But i got no idea how to translate it to dql.

I have tried to write such function in Repository:

    {

        $entityManager = $this->getEntityManager();


        $query = $entityManager->createQuery(
            'SELECT distinct she
            FROM App\Entity\Shelfs she
            JOIN App\Entity\Racks rac
            JOIN App\Entity\Rows row
            JOIN App\Entity\Warehouses war
            WHERE she NOT IN (SELECT prod FROM App\Entity\Products prod)
            ');
        return $query->getResult();

    }

That is partialy working as at first it showed the exact values that sql query did. For example 3 Shelf id with out products on them. But when I added product to one of those shelf it still shows that 3 are empty.

Thats my controller:

public function index()
    {
        $repository = $this->getDoctrine()->getRepository(Products::class);
        $product = $repository->find(76); // that's for testing and it is refreshing

        $warehousesRepository = $this->getDoctrine()
            ->getRepository(Warehouses::class);

        $freespace = $warehousesRepository->findempty(); // that is not refreshing


        return $this->render('base.html.twig', [
            'selected_view' => 'pages/findPlace.html.twig',
            'product' => $product,
            'freespace' => $freespace

        ]);
    }

Here is my Products Entity:

id;
    }

    public function getBarcode(): ?int
    {
        return $this->barcode;
    }

    public function setBarcode(int $barcode): self
    {
        $this->barcode = $barcode;

        return $this;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    public function getPrice(): ?float
    {
        return $this->price;
    }

    public function setPrice(float $price): self
    {
        $this->price = $price;

        return $this;
    }

    public function getQuantity(): ?int
    {
        return $this->quantity;
    }

    public function setQuantity(int $quantity): self
    {
        $this->quantity = $quantity;

        return $this;
    }

    public function getWarehouseId(): ?int
    {
        return $this->warehouse_id;
    }

    public function setWarehouseId(int $warehouse_id): self
    {
        $this->warehouse_id = $warehouse_id;

        return $this;
    }

    public function getRowId(): ?int
    {
        return $this->row_id;
    }

    public function setRowId(int $row_id): self
    {
        $this->row_id = $row_id;

        return $this;
    }

    public function getRackId(): ?int
    {
        return $this->rack_id;
    }

    public function setRackId(int $rack_id): self
    {
        $this->rack_id = $rack_id;

        return $this;
    }

    public function getShelfId(): ?int
    {
        return $this->shelf_id;
    }

    public function setShelfId(int $shelf_id): self
    {
        $this->shelf_id = $shelf_id;

        return $this;
    }
}

I tried to make relations after creating entities by adding:

* @ORM\ManyToOne(targetEntity="Warehouses")
* @ORM\JoinColumn(name="warehouse_id", referencedColumnName="id")

but that didn't work so i guess i need to redo my entities and specify relations in the wizzard.

I have made relations using sql:

ALTER TABLE shelfs ADD FOREIGN KEY (rack_id) REFERENCES racks(id);
        ALTER TABLE racks ADD FOREIGN KEY (row_id) REFERENCES rows(id);
        ALTER TABLE rows ADD FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);
        ALTER TABLE products ADD FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);
        ALTER TABLE products ADD FOREIGN KEY (rack_id) REFERENCES racks(id);
        ALTER TABLE products ADD FOREIGN KEY (row_id) REFERENCES rows(id);
        ALTER TABLE products ADD FOREIGN KEY (shelf_id) REFERENCES shelfs(id);
        ALTER TABLE order_items ADD FOREIGN KEY (product_id) REFERENCES products(id);
        ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders(id);
        ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

It should look something like this:

I feel like i got lost in the process somewhere.

I got no idea what's happening and i cannot find anything related in symfony documentation.

Fetching data from Symfony Doctrine DB

Answers (1)

Related Questions