CODEWITHSUNDEEP
symfonydoctrine-orm
Deep
Deep

Reputation: 55

Symfony | Doctrine Help Needed - Inserting in Multiple Tables

I have 3 tables as:

  1. products - product_id, product_name, price, type, create_time
  2. gateways - gateway_id, gateway_name, create_time
  3. mapping_product_gateways - mapping_id, gateway_id, product_id

The products and gateways tables have nothing in common. No foreign keys etc. But when I create a product, I show the gateway selection field as multi-select field that is populated with data from gateways table. Once I hit the submit button to insert the product record, at that time all the selected gateway’s IDs are inserted in mapping_product_gateways table with the same product_id and selected gateways id.

So after insertion the record may look like as:

Products table record:

1, Product 1, $10, , digital, 10/10/2013

2, Product 2, $13, subscription, 01/01/2013

Gateways table record:

1, Paypal, 03/01/2014

2, Credit Card, 01/01/2014

3, 2Checkout, 02/01/2014

So, mapping_product_gateways record may look like:

mapping_id, gateway_id, product_id

1, 2, 1

2, 3, 1

3, 1, 2

3, 2, 2

That means Product ID 1 has Gateways 2 & 3 associated with it and Product ID 2 has Gateways 1 & 2 associated with it.

Since there's no direct relationship between the mapping and product table, how to build query in repository class to do search, insert, update of the record? Or do I need to create onetomany type of relationship n entity?

I am totally confused..excuse me but am very new to symfony.

Thanks

Upvotes: 0

Views: 1704

Answers (2)

Tobias
Tobias

Reputation: 954

What you are still missing are the setters and getters (example for product -> gateways):

public function addGateway(GatewayInterface $gateway){
    $this -> gateways -> add($gateway);
    return $this;
}

public function setGateways(Collection $gateway){
    $this -> gateways = $gateways;
    return $this;
}

public function getGateways(){
    return $this -> gateways;
}

You need to create the GatewayInterface first to get it work. And you need the same setters / getters for gateway -> products

Index action (create a product) must look like this:

public function indexAction()
{
    $em = $this->getDoctrine()->getManager();
    $product = new ShopProducts();
    $em -> persist($product);
    $gateway = new GlobalGateways();
    $em -> persist($gateway);
    $product -> addGateway($gateway);
    $em -> flush();
}

Note: You should always name your classes in singular. So it would be better if the names were "Product" and "GlobalGateway"

Upvotes: 0

Tobias
Tobias

Reputation: 954

This question is not a symfony question, it's a doctrine question. First of all you should not ask yourself "how will the database look like?". The better question is: "How are products and gateways associated?"

As I understand your post: - Many products can have many gateways.

So we're talking about a many-to-many association. That's the reason why you need ("how will the database look like?") a third table that associates both tables. But we need to answer the second question ("How are products and gateways associated?") Answer: They are associated via a many-to-many association.

How is this achieved in doctrine?

/**
* @ORM\Entity()
*/
class Product{
    /*
    * @ORM\ManyToMany(targetEntity="Gateway", inversedBy="products")
    */
    private $gateways;
    //adder + setter + getter
}

/**
* @ORM\Entity()
*/
class Gateway{
    /*
    * @ORM\ManyToMany(targetEntity="Product", mappedBy="gateways")
    */
    private $products;
    //adder + setter + getter
}

To schema update do php app/console doctrine:schema:update --force in your cmd (app root dir)

This is the way, doctrine solves the problem. Nice, isn't it?

Your second question was: How can I CRUD with the entities?

Let's say your coding in a controller. You database has no products, but 5 gateways (ids 1-5)

class someController extends Controller {
    public function doSomeCrud(){
        $em = $this -> get('doctrine.orm.entity_manager');
        $product = new Product();
        $someGateway = $em -> find('NAMESPACE\Gateway',3);
        $anotherGateway = $em -> find('NAMESPACE\Gateway',5);
        $product -> addGateway($someGateway) -> addGateway($anotherGateway);
        $em -> persist($product);
        $em -> flush();
    }
}

After calling the method, your database will look like:

Product: 1

Gateway: 1;2;3;4;5

Product_Gateway:(1,1,3);(2,1,5)

You can get all gateways of a product via 

$product -> getGateways();

and update a product via:

$product -> getGateways() -> first() -> setName('new name');
$em -> flush();

The entity manager recognized that the name of gateway 3 has changed and makes a UPDATE Gateway set name='new name' WHERE id = 3

Upvotes: 1

Related Questions