CODEWITHSUNDEEP
mysqlsymfonydoctrine
sisko
sisko

Reputation: 9900

Symfony2 multi-table join issues

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.

Upvotes: 0

Views: 29

Answers (1)

user2268997
user2268997

Reputation: 1391

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

Upvotes: 1

Related Questions