Symfony2 multi-table join issues

Question

I'm a beginner with Symfony.

I have 2 entities: Stock and Sales. Both have their associated database tables.

Stock has the following structure:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | NO   |     | NULL    |                |
| description | varchar(255) | NO   |     | NULL    |                |
| input       | int(11)      | NO   |     | NULL    |                |
| output      | int(11)      | NO   |     | NULL    |                |
| balance     | int(11)      | NO   |     | NULL    |                |
| created     | datetime     | NO   |     | NULL    |                |
| updated     | datetime     | NO   |     | NULL    |                |
| unitcost    | double       | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Sales has the following structure:

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| cost       | double   | NO   |     | NULL    |                |
| date       | datetime | NO   |     | NULL    |                |
| profitloss | double   | NO   |     | NULL    |                |
| sid        | int(11)  | YES  | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+

I am attempting to create a one-to-many relationship between Stock and Sales.

Stock is a list of all items for sale in a store. Sales is the record of actual purchases of any item in the Stock database table.

It was in the process of displaying my first Sales item that I experienced a major problem.

To display a Sale, my vision was to execute an SQL statement between both table where Sales:sid == Stock:id - I ran into newbie problems as I couldn't find a straight forward way of doing the kind of SQL execution I have become accustomed to.

How can I make a start on this? I want to print details of each sale by fetch related data from the Stock table.

user2268997 · Accepted Answer · 2015-01-11 07:12:55Z

1

Doctrine handles this sort of thing for you.You can create a Bidirectional one-to-many association as in One-to-Many Association. so Your stock Entity will be like

class Stock{
 ...

 /**
 *@OneToMany(targetEntity="YourBundleNamespace\Entity\Sale", mappedBy="stock")
 *
 */
 protected $sales;

}

class Sale{  

protected $id;
....
/**
*@ManyToOne(targetEntity="YourBundleNameSpace\Entity\Stock", inversedBy="sales")
*@JoinColumn(name="sid", referencedColumnName="id")
*/
protected $stock;

}

then simply find your desired Sale Entity Controller and call $sale->getStock() and you will get the Stock Entity associated with that sale.

For sql Execution doctrine has the ability to execute raw sql but it's better that you use doctrine's dql and QueryBuilder. see dql and QueryBuilder

answered Jan 11, 2015 at 7:12

user2268997

1,4112 gold badges16 silver badges43 bronze badges

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

1 Comment

sisko Over a year ago

Thank you. I was having difficulty setting that up correctly.

Collectives™ on Stack Overflow

Symfony2 multi-table join issues

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related