CodeIgniter for Symfony2 with Doctrine2
I'm really having a hard time converting this query to Doctrine:
public function get_order($id)
{
$this->db->select('*');
$this->db->from('tbl_orderline');
$this->db->join('tbl_order', 'tbl_order.orderNo = tbl_orderline.orderNo');
$this->db->join('tbl_customer', 'tbl_customer.customerNo = tbl_order.customerNo');
$this->db->join('tbl_product', 'tbl_product.productNo = tbl_orderline.productNo');
$this->db->where('tbl_order.orderNo', $id);
$query = $this->db->get();
return $query->result_array();
}
Could you help me with this? Any suggestions? Thanks to
+3
source to share
2 answers
// if you're currently in custom Repository class then:
$qb = $this->createQueryBuilder('ol');
// if you're in a controller, then should be:
$qb = $em->getRepository('AppBundle:OrderLine')->createQueryBuilder('ol'); // Or whatever your bundle name is.
// query alias legend:
// ol - order line
// o - order
// c - customer
// p - product
// Your query builder should look something like this:
$qb
->addSelect('o, c, p')
->leftJoin('ol.order', 'o') // this is your relation with [order]
->leftJoin('o.customer', 'c') // this is your relation with [customer] from [order]
->leftJoin('ol.product', 'p') // this is your relation with [product] from [order line]
->where($qb->expr()->eq('ol.id', ':orderLineId')
->setParameter('orderLineId', $id)
->getQuery()
->getOneOrNullResult();
Note:
Since you haven't provided any entity mappings, this is completely unexpected. You will most likely want to change the properties in this query, but at least that should give you the start you need.
Feel free to ask if you don't understand something.
+2
source to share
I've always found it easier to write in direct dql . Trying to use querybuilder for tricky things drives me crazy. This obviously requires you to have the correct relationships mapped in your entities, either annotated or with an orm file.
Obviously its hard to verify what I've put below, so you might need to debug a bit.
$query = $this->getEntityManager()->createQuery(
'select orderline, order, customer, product
from BundleName:tlb_orderline orderline
join orderline.orderNo order
join order.customerNo customer
join orderline.productNo product
where order.orderNo = :id');
$query->setParameter('id' => $id);
return $query->getResult();
+1
source to share