I am learning from this Question but facing issues with many operations in between:
doctrine 2 query builder and join tables
Below is my Questions. I am looking for help in my previous question too.. Thanks for those who helped me in solving the issues.
I am creating a big Query in Doctrine 2.3, The operations are not familiar to me.But I learnt with the help of many persons. Currently I am facing issue with Inner Joint between 3 tables.
My Joint:
SELECT *
FROM user AS u
LEFT JOIN source AS s ON u.user_source_fk=s.source_id
LEFT JOIN area AS a ON s.source_node_fk = a.area_id;
The above Query is what I am trying to convert in Doctrine.
I tried to give the query as like this but it is not working:
SELECT a FROM Ipf\Model\User u LEFT JOIN Ipf\Model\Sources ON u.user_source_fk=s.source_id LEFT JOIN Ipf\Model\Area aa ON s.source_node_fk = aa.area_id;
How My Original Query will be:
1 Doctrine JOIN Query:
SELECT *
FROM user AS u
LEFT JOIN source AS s ON u.user_source_fk=s.source_id
LEFT JOIN area AS a ON s.source_node_fk = a.area_id;
+
SELECT * FROM user WHERE
(`user_name` like '%TOM%' OR `user_name` like '%AN%' and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59') OR
NOT ( --NOR
(`user_name` like '%PHP%' OR `user_name` like '%BA%' and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59') OR
(`user_name` like '%SUN%' OR `user_name` like '%MOON%' and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59')
) OR
NOT ( --NAND
(`user_name` like '%RAJ%' OR `user_name` like '%MUTH%' and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59') AND
(`user_name` like '%BAG%' OR `user_name` like '%LAP%' and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')
)
I want to do Inner Joint to the Query with the above Query that is Inner Joint with Operators Refer:Multiple Query in Doctrine with NAND,NOR,NOT,AND Operators
How to Join the tables and execute the Query?
2 Doctrine Query Generation:
$qry = $this->manager()->createQueryBuilder()
->from($this->entity, 'e')
->select('e');
// (`user_name` like '%TOM%' OR `user_name` like '%AN%' and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59')
$expr1 = $qry->expr()->andX(
$qry->expr()->orX(
$qry->expr()->like('e.user_name', '%TOM%'),
$qry->expr()->like('e.user_name', '%AN%')
),
$qry->expr()->between('e.login_datetime', '2013-02-01 00:00:00', '2013-02-31 23:59:59')
);
//(`user_name` like '%PHP%' OR `user_name` like '%BA%' and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59')
$expr2a = $qry->expr()->andX(
$qry->expr()->orX(
$qry->expr()->like('e.user_name', '%PHP%'),
$qry->expr()->like('e.user_name', '%BA%')
),
$qry->expr()->between('e.login_datetime', ''2013-02-01 00:00:00'', '2013-03-31 23:59:59')
);
// (`user_name` like '%SUN%' OR `user_name` like '%MOON%' and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59')
$expr2b = $qry->expr()->andX(
$qry->expr()->orX(
$qry->expr()->like('e.user_name', '%SUN%'),
$qry->expr()->like('e.user_name', '%MOON%')
),
$qry->expr()->between('e.login_datetime', '2013-03-01 00:00:00', '2013-04-31 23:59:59')
);
// combine expr2a and expr2b with OR as $expr2
$expr2 = $qry->expr()->orX($expr2a, $expr2b);
// (`user_name` like '%RAJ%' OR `user_name` like '%MUTH%' and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59')
$expr3a = $qry->expr()->andX(
$qry->expr()->orX(
$qry->expr()->like('e.user_name', '%RAJ%'),
$qry->expr()->like('e.user_name', '%MUTH%')
),
$qry->expr()->between('e.login_datetime', ''2013-04-01 00:00:00'', '2013-06-31 23:59:59')
);
// (`user_name` like '%BAG%' OR `user_name` like '%LAP%' and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')
$expr3b = $qry->expr()->andX(
$qry->expr()->orX(
$qry->expr()->like('e.user_name', '%BAG%'),
$qry->expr()->like('e.user_name', '%LAP%')
),
$qry->expr()->between('e.login_datetime', '2013-05-01 00:00:00', '2013-07-31 23:59:59')
);
// combine expr2a and expr2b with OR as $expr2
$expr3 = $qry->expr()->andX($expr3a, $expr3b);
// final query essentially WHERE expr1 OR NOT(expr2) OR NOT(expr3)
$qry->where($expr1)
->or($qry->expr()->not($expr2))
->or($qry->expr()->not($expr3));
How can I Modify to Achieve the Above Query with JOIN Operation?
Can some one help me to solve my issue. Its nightmare for me in doctrine....