CODEWITHSUNDEEP
symfony
benoliver999
benoliver999

Reputation: 165

How to pull information based on an ID?

I have a entity Article20000Information, with a few fields like: id, description, manufacturer_id, supplier_id

I have another entity, Organisation. It has a list of companies (both manufacturers & suppliers) each with an id.

I also have a page which renders a list of Article20000Information data. Currently, it simply displays the data in the table so:

| id | Description | Manufacturer | Supplier | Price  |
|----|-------------|--------------|----------|--------|
| 1  |   thing1    |      2       |    5     |  34    |
| 2  |   thing2    |      5       |    2     |  23    |
| 3  |   thing3    |      3       |    4     |  25    |

What I need is for the manufacturer and supplier column to display the name value from the organisation table, based on the id shown.

What is the best way to go about this?

Upvotes: 0

Views: 47

Answers (3)

benoliver999
benoliver999

Reputation: 165

Got it!

I needed multiple aliases, which I'd guessed, but I also needed to give them AS so that they come out with different column names. This in turn lets twig render the tags.

<?php

namespace Regenerys\QMSBundle\Entity;

use Doctrine\ORM\EntityRepository;

class Article20000InformationRepository extends EntityRepository
{
    public function findStuff()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT 
    A.id,
    A.articleNumber,
    A.description,
    B.name as manufacturer,
    C.name as supplier
FROM 
    RegenerysQMSBundle:Article20000Information A
    LEFT OUTER JOIN RegenerysQMSBundle:Organisation B WITH B.id = A.manufacturer 
    LEFT OUTER JOIN RegenerysQMSBundle:Organisation C WITH C.id = A.supplier '

            )
            ->getResult();
    }
}

Thanks to @Alexandru for his DQL help.

Upvotes: 1

Alexandru Olaru
Alexandru Olaru

Reputation: 7092

If you are using doctrine, a proper way is to create a Repository class, and there write your joined code proposed by @K139 but in DQL: 

class Article20000InformationRepository extends EntityRepository
{
    public function findAll()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT A.id, A.Description, B.ManufacturName, B.supplierName FROM AppBundle:Article20000Information A
                 LEFT OUTER JOIN AppBundle:Organisation B ON B.id = A.id '
            )
            ->getResult();
    }
}

Then in your controller you will use it:

$articles = $em->getRepository('AppBundle:Article20000Information')->findAll();

Upvotes: 0

K139
K139

Reputation: 3669

You need to join the two tables based on id condition.

select A.id, A.Description, B.ManufacturName, B.supplierName
from Article20000Information A
left outer join Organisation B
ON B.id = A.id

More info on table joins.

Upvotes: 0

Related Questions