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:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\ProductsRepository")
 */
class Products
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="integer")
     */
    private $barcode;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $name;

    /**
     * @ORM\Column(type="float")
     */
    private $price;

    /**
     * @ORM\Column(type="integer")
     */
    private $quantity;

    /**
     * @ORM\Column(type="integer")
     * @ORM\ManyToOne(targetEntity="Warehouses")
     * @ORM\JoinColumn(name="warehouse_id", referencedColumnName="id")
     */
    private $warehouse_id;

    /**
     * @ORM\Column(type="integer")
     * @ORM\ManyToOne(targetEntity="Rows")
     * @ORM\JoinColumn(name="row_id", referencedColumnName="id")
     */
    private $row_id;

    /**
     * @ORM\Column(type="integer")
     * @ORM\ManyToOne(targetEntity="Racks")
     * @ORM\JoinColumn(name="rack_id", referencedColumnName="id")
     */
    private $rack_id;

    /**
     * @ORM\Column(type="integer")
     * @ORM\ManyToOne(targetEntity="Shelfs")
     * @ORM\JoinColumn(name="shelf_id", referencedColumnName="id")
     */
    private $shelf_id;

    public function getId(): ?int
    {
        return $this->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.

please post your entity code so we can determine the associations. When using doctrine you would use the associated relationships between the entities like JOIN she.racks AS rac and JOIN racks.rows AS row. Otherwise when specifying the entity namespace, you would specify a join clause JOIN App\Entity\Racks AS rac WITH she.rackId = rac.id — Will B.
– Will B., Commented Dec 8, 2019 at 14:08
As for tutorials, SO is not really appropriate to ask for such things. However SymfonyCasts is a great resource. — Will B.
– Will B., Commented Dec 8, 2019 at 14:12
You can use SQL with Doctrine with Native Queries and ResultSetMapper, if that helps. Also, you might want to rethink your models. Since the price is attached to products, but not order items changing a product's price will (retroactively) change the price of order items leading to wrong orders. — dbrumann
– dbrumann, Commented Dec 8, 2019 at 19:44
agreed, ordered items price and name at least should be static entries to avoid the relationship being updated when the related product is changed. Symfony is just a framework that allows you to utilize Doctrine ORM to map your database with. The ORM is meant specifically for business logic, as a means to be more convenient. For reporting purposes stick to native SQL queries. As for a tutorial go through the Doctrine tutorial — Will B.
– Will B., Commented Dec 9, 2019 at 6:06
You only need to specify the ORM\JoinColumn and not also the ORM\Column, doctrine will read the annotations for the referencedColumnName entity property. It is also encouraged to map out your entities and their relationships first and then execute the doctrine:schema:update command (or use doctrine migrations) to create the table structure in your database. — Will B.
– Will B., Commented Dec 9, 2019 at 15:18

TsV · Accepted Answer · 2019-12-09 09:12:17Z

1

    $sel = $this->createQueryBuilder('prod') // FROM App\Entity\Products prod
        ->select('prod')
        // ->from('App\Entity\Products', 'prod'); // FROM App\Entity\Products prod
    ;


    $res = $this->createQueryBuilder('she') // FROM App\Entity\Shelfs she
        ->select('DISTINCT she')
        // ->from('App\Entity\Shelfs', 'she')  // FROM App\Entity\Shelfs she
        ->leftJoin('she.rac', 'rac')
        ->leftJoin('she.row', 'row')
        ->leftJoin('she.war', 'war')
        ->andWhere('she NOT IN :(sel)')->setParameter('sel', $sel->getQuery()->getResult());
        ;

    return $res->getQuery()->getResult();

answered Dec 9, 2019 at 9:12

TsV

6394 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetching data from Symfony Doctrine DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related